Count All Things Happened Today

Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John

check out the year, month and day or datepart functions in t-sql.

You can assemble the date anyway you want.

|||

forgot to add:

SET DATEFORMAT

CONVERT

|||

i found this : http://support.microsoft.com/kb/q186265/

which helps me get stuff like secounds etc :

 Ms for Milliseconds
 Yy for Year
 Qq for Quarter of the Year
 Mm for Month
 Dy for the Day of the Year
 Dd for Day of the Month
 Wk for Week
 Dw for the Day of the Week
 Hh for Hour
 Mi for Minute
 Ss for Second

how do i get it as a short date string, like "11/10/2007" please John

|||

Try this:

WHERE timecreated>=DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 0)AND timecreated<DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 1)

|||

that worked perfectly, is it "read" by like this :

WHERE TimeCreated is equalto or greater than today + 0 days. AND TimeCreated is LessThan today + 1day.

just wondering becuase i wish to adjust this code to count things happening this week / month etc.

Thanks John

|||

Both DATEADD and DATEDIFF are very useful functions to work with datetime in TSQL. You can search to find more information on these two key words. You can modify the code with the same logic to get what you want, but i have read some good articles with a lot usages for these two functions.

|||

yeh they seem to be invalueable when attempting to work with dates / times! thanks for this! John