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
