19

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

Matt
  • 4,140
  • 9
  • 40
  • 64

8 Answers8

25

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

Oscar Gomez
  • 18,436
  • 13
  • 85
  • 118
8

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'

ysrb
  • 6,693
  • 2
  • 29
  • 30
  • 3
    +1, this is how you do it. If you just go for 23:59:59 you overlook the entire second between 23:59:59.000 and 23:59:59.999. Even if you specify the milliseconds, using < rather than <= is still just cleaner. – Joel Coehoorn Jul 21 '11 at 03:27
  • @Joel Coehoorn That is correct however if you are given a date that is not 00:00:00, you will first have to truncate it and then add 1 day, and then it doesn't look cleaner than substracting 3 ms and using <=, additionally it forces you to use < and in my opinion between is much cleaner and because it is inclusive you would have to use the last millisecond. – Oscar Gomez Jul 21 '11 at 03:31
  • @ysrb: There is just one problem: if you WHERE mydate < (day+1) you'll have an overflow on 99991231. – Stefan Steiger Dec 04 '19 at 08:58
8

Try this, it could be helpful for you I use one of these two ways to work with time portion in DATETIME fields to do comparisons EX: get a user log for one day, i.e. from Today's date at 12:00:00 AM till Today's date but at 12:00:00 PM

DECLARE @FromDate datetime
DECLARE @ToDate datetime

SET @FromDate = GETDATE()
SET @ToDate = GETDATE()
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate
SET @FromDate = CONVERT(DATETIME, CONVERT(varchar(11),@FromDate, 111 ) + ' 00:00:00', 111) 
SET @ToDate = CONVERT(DATETIME, CONVERT(varchar(11),@ToDate, 111 ) + ' 23:59:59', 111)
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate



DECLARE @TEST_FROM DATETIME
SET @TEST_FROM = dateadd(month,((YEAR(@FromDate)-1900)*12)+MONTH(@FromDate)-1,DAY(@FromDate)-1) + ' 12:00:00'

DECLARE @TEST_TO DATETIME
SET @TEST_TO = dateadd(month,((YEAR(@ToDate)-1900)*12)+MONTH(@ToDate)-1,DAY(@ToDate)-1) + ' 23:59:59'

Print '------------------------ '
PRINT @TEST_FROM
PRINT @TEST_TO

This will print the following in SQL Query editor screen

------------------------ 
Dec 28 2011  3:18PM
Dec 28 2011  3:18PM
------------------------ 
Dec 28 2011 12:00AM
Dec 28 2011 11:59PM
------------------------ 
Dec 28 2011 12:00PM
Dec 28 2011 11:59PM

References The way using the convert is from my experience, the other way is from this link http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx Have fun :)

sameh.q
  • 1,691
  • 2
  • 23
  • 48
  • +1 for this method. I love it! Can I ask you, how to add milliseconds? For example, I want the end date to `2018/09/09 59:59:59.990` to add *990 ms*. Can I simply add *.990* to the end of *59:59:59*? Thanks again! – Sam Sep 12 '18 at 04:02
  • @Sam, try this [post](https://stackoverflow.com/questions/5312205/how-to-print-getdate-in-sql-server-with-milliseconds-in-time), it is helpful – sameh.q Sep 13 '18 at 09:51
  • While the 23:59:59' is not slick, a person supporting your code will know exactly what you are doing. Sure you can do the dateadd ms solution, but a junior dev might not know what you are doing. Something to consider. – Warren LaFrance Dec 10 '19 at 20:12
5

Try this query for using Datetime datatype to get

2018-01-29 23:59:59.997

select dateadd(ms, -3, (dateadd(day, +1, convert(varchar, GETDATE(), 101))))
Dhana
  • 1,618
  • 4
  • 23
  • 39
1
declare @myDate DateTime, @lastMonth DateTime,  @thisMonth DateTime
set @myDate = GETDATE()
set @lastMonth = DateAdd(month, -1, CAST(@myDate as Date))
set @thisMonth = DateAdd(day, -DatePart(day, @myDate)+1, CAST(@myDate as Date))

select @myDate as MyDate, DateAdd(day, -DatePart(day, @lastMonth) + 1, @lastMonth) FirstDay, DateAdd(second, -1, @thisMonth) LastDay

Results

Xile
  • 23
  • 6
1

Try This:

SELECT dateadd(millisecond,-1,cast(cast(getdate() AS date) AS datetime2))
Mohammad Reza Shahrestani
  • 1,119
  • 3
  • 17
  • 28
Abd Al-Kareem Attiya
  • 53
  • 6
1
SELECT DATEADD(ms, -2,  CAST(CONVERT(date, DATEADD (DAY,1,getdate())) AS varchar(10)))

output: yyyy-mm-dd 23:59:59.997

2020-08-31 23:59:59.997

barış çıracı
  • 1,033
  • 14
  • 16
0

I hope someone finds this useful

declare  @TodaysDate smalldatetime
declare @TodaysDatepm smalldatetime

First I get the date and Time as of Midnight i.e 16/05/2021 12:00 am

set @TodaysDate = DATEADD(minute, 0,CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime))

Then I add 23hours and 59 minutes onto it i.e (60*23)+59 Which gives 1439, from there I use the the dateadd function

set @TodaysDatepm =DATEADD(minute, 1439, @TodaysDate)

This will always print out midnight of what you set in @TodaysDate

Print @TodaysDatepm
AliNajafZadeh
  • 1,216
  • 2
  • 13
  • 22
Mukiibi Ismail
  • 41
  • 2