Eliminate Time from DateTime!

[Bwintech]

Hi,

I have a table that stores entries for counts every 10 minutes for each day.

The day format is datetime and includes the time at 10 minute intervals, I want to group them by date and then sum the counts per date.

Ive been trying to do this but cant find a suitable data type to convert to?

I would appreciate any help!

 

[Andles]

Try using this which seems to give the result you require as I understand it to be.
This gives the format "dd mmm yyyy"
See BOL under "Convert" for other formats you may require.

SELECT Convert(VarChar(12),YourDateField,106)
FROM YourTable
GROUP BY Convert(VarChar(12),YourDateField,106)

Cheers

AJ

 

[Bwintech]

Brilliant, that worked. Thank you very much. The only thing now is that it doesnt see it as a date anymore which doesnt allow me to sort it by date - any ideas?

Also, I want to know also seperate only the times - how would I do that?

Thanks again Andles

 

[tlbroadbent]

Use one of the date styles that has yymmdd format . These are 11, 12, 20, 21, 111, 112, 120, or 121. Style 106 is dd mon yyyy format and doesn't sort properly.

You can sort by the converted date or by ordinal number.

Select
convert(char(10), tblDateTime, 11) As TheDate,
Sum(tblCount) As TotCnt
From TableName
Group By convert(char(10), tblDateTime, 11)
Order By convert(char(10), tblDateTime, 11)

Alternate: Use ordinal number for sort

Select
convert(char(10), tblDateTime, 11) As TheDate,
Sum(tblCount) As TotCnt
From TableName
Group By convert(char(10), tblDateTime, 11)
Order By 1 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.