12 Mar

SQL Server Query

Posted by mike under enart.hubeidaxue.com

  • In Microsoft SQL Server, how can I group data from a datetime column based solely on the DATE (but not the time). Consider the following information: 09/20/05 03:15pm 09/20/05 03:16pm 09/20/05 03:17pm 09/21/05 11:30am 09/21/05 11:31am The desired results are: 09/20/05 3 rows 09/21/05 2 rows How can I use a CAST, CONVERT or similar operator such that the GROUP BY clause in my SELECT statement will produce the desired output? Thank you!


  • Hi, try my query: select convert(varchar(10), ,103), convert(varchar,count(*)) + ' rows' from group by convert(varchar(10), ,103) While: is your field/column represent the datetime value is your table name. For example: select convert(varchar(10), myDate,103), convert(varchar,count(*)) + ' rows' from myTable group by convert(varchar(10), myDate,103) Cheer..


  • Hi awilinsk, you query very simple. great! bala


  • This query will display by order. SELECT CONVERT(char(12), DATEFIELD, 1) as date,count(CONVERT(char(12), DATEFIELD, 1)) as count ,' rows' as result from TABLENAME group by CONVERT(char(12), DATEFIELD, 1) order by CONVERT(char(12), DATEFIELD, 1) desc DATEFIELD - your date field name TABLENAME - your table name Thanks Bala


  • SELECT DATEPART (m, YourDateField) As Month, DATEPART (d, YourDateField) as Day, DATEPART (yy, YourDateField) as Year, Count (YourDateField) as Count, ' rows' from YourTable Group By DATEPART (m, YourDateField), DATEPART (d, YourDateField), DATEPART (yy, YourDateField) putting in the title of your date column in place of YourDateField and your table name in place of YourTable. That should work - shame I can't post it as an answer... bala has the right idea but didn't carry out the grouping to get rid of times... No need to Cast or Convert, which will only confuse... AG


  • Hi How abt the below qry SELECT date1, count(date1) as rows from table1 group by date1 thanks bala


  • hi, convert your date column to varchar(10) and with style 103 or 3 and then use group by to find count select convert(varchar(10),YourDate,103), count(*), ' rows' from YourTable group by convert(varchar(10),YourDate,103) i think it will give u the result


  • This is great, thanks !!!


  • Hi chrisasking, This query will give you the output. The problem is it will display one more column like order1. Let me know what do you think. SELECT CAST(Substring(CONVERT(char(12), curdate, 1) ,7,2) as int) as order1, CONVERT(char(12), curdate, 1) as date,count(CONVERT(char(12), curdate, 1)) as count ,' rows' as result from table1 group by CONVERT(char(12), curdate, 1),CAST(Substring(CONVERT(char(12), curdate, 1) ,7,2) as int) order by CAST(Substring(CONVERT(char(12), curdate, 1) ,7,2) as int) curdate - datefield table1 - table name Thanks Bala


  • The most recent suggestion seems to arrange the result set in date order incorrectly, in that the rows are displayed by month as the primary sort, rather than the year (in other words, 12/03/03 appears at the top of the query, whereas 10/01/04 appears much lower down). Can this be fixed? Thanks


  • The output displays the date and their counts, so I thought not necessary to check the time. Thanks Bala


  • sorry, mine wont work. thanks bala


  • Hi Check out this query. SELECT CONVERT(char(12), DATEFIELD, 1),count(CONVERT(char(12), DATEFIELD, 1)),' rows' as result from TABLENAME group by CONVERT(char(12), DATEFIELD, 1) thanks bala


  • SELECT CONVERT(VARCHAR,date_field,1) as date_field FROM table_name GROUP BY CONVERT(VARCHAR,date_field,1); You can also change the format of the date that comes out by changing the third parameter. Here are the formats http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about SQL Server Query , Please add it free.
    | |