Menu
I'm trying to do a where statement that specifies a DateTime field is between the start and end of the previous month.
To do this, I need to specify that the first day of the previous month has a time of 00:00:00 and the last day of the previous month has a time of 23:59:59.
This second condition is giving me a headache..
Can someone help me out?
Cheers
MSSQL 2008
401
string dt1 = DateTime. Now. ToString("yyyy-MM-dd 23:59:59.000"); Also in your case if suppose your input datetime having any hour/minute details then adding timespan could give wrong results, but if you hardcode as i have done above it will always return date in your required way.
To convert a datetime to a date, you can use the CONVERT() , TRY_CONVERT() , or CAST() function.
Definition and Usage. The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format.
try:
SELECT DATEADD(ms, -3, '2011-07-20')
This would get the last 23:59:59 for today.
why 3 milliseconds?, this is because Microsoft SQL Server DATETIME columns have at most a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds
148
You can also use the less than '<' without the equal. So that you don't need 23:59:59.
Eg. WHERE DateCreated < '20111201 00:00:00'
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
