Building of Date is wrong

Question

Building of Date is wrong

dwilliscp

SSCrazy Eights

Points: 9270
More actions
June 8, 2012 at 8:29 am

#257569

I can not seem to figure out what I did wrong.. this does not return any records...( trying to build a statement that will get the 5th day of the next month )
select Ship_date
from shipment_trends
where Ship_date = DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))
There is data there.. if I hard code in the 5th of next month.
FYI: ship_date has all times stored as midnight.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dwilliscp (6/8/2012)
I can not seem to figure out what I did wrong.. this does not return any records...( trying to build a statement that will get the 5th day of the next month )
select Ship_date
from shipment_trends
where Ship_date = DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))
There is data there.. if I hard code in the 5th of next month.
FYI: ship_date has all times stored as midnight.

The following will return the 5th of the following month:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 4)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

dwilliscp (6/8/2012)
I can not seem to figure out what I did wrong.. this does not return any records...( trying to build a statement that will get the 5th day of the next month )
select Ship_date
from shipment_trends
where Ship_date = DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))
There is data there.. if I hard code in the 5th of next month.
FYI: ship_date has all times stored as midnight.

run the following and you will see the reason for your error

select DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))

MVDBA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

dwilliscp (6/8/2012)
I can not seem to figure out what I did wrong.. this does not return any records...( trying to build a statement that will get the 5th day of the next month )
select Ship_date
from shipment_trends
where Ship_date = DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))
There is data there.. if I hard code in the 5th of next month.
FYI: ship_date has all times stored as midnight.

-- doesn't work:

SELECT DATEADD(MM,1,Convert(datetime, (Month(getdate())+'-'+'05'+'-'+YEAR(getdate()))))

-- does work

SELECT DATEADD(MM,1+DATEDIFF(MM,0,GETDATE()),4)

Oops about 10 folks beat me to it

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

try the following

declare @mydate datetime

set @mydate=GETDATE()

SELECT dateadd(dd,4,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) )

see Pinal Dave's blog post on these

http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/

edit: the other suggestions are better than mine - i did a cut an poste from t-internet

MVDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

michael vessey (6/8/2012)
try the following
declare @mydate datetime
set @mydate=GETDATE()
SELECT dateadd(dd,4,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) )
see Pinal Dave's blog post on these
http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/
edit: the other suggestions are better than mine - i did a cut an poste from t-internet

You can avoid the character conversions. Take a look at this blog post: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. You will probably find those routines quite useful.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 6

Ok.. I ran the statement "SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 4)

" and it does return the 5th of next month.

However I do not understand how it is doing that... by my understanding of the two commands is this:

DateDiff(datepart to compare, start, end)

DATEADD(datepart, amount to add, date)

So looking just at the DateDiff it should return 6 for 6 months crossed between 0 and June 8 2012.

So looking at the above statement we are changing the month (datepart) by the DateDiff return of 6+1 to the date of 4... see my confusion?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

dwilliscp (6/8/2012)
Ok.. I ran the statement "SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 4)
" and it does return the 5th of next month.
However I do not understand how it is doing that... by my understanding of the two commands is this:
DateDiff(datepart to compare, start, end)
DATEADD(datepart, amount to add, date)
So looking just at the DateDiff it should return 6 for 6 months crossed between 0 and June 8 2012.
So looking at the above statement we are changing the month (datepart) by the DateDiff return of 6+1 to the date of 4... see my confusion?

Maybe this will help, I'll rewrite it using dates:

SELECT DATEADD(mm, DATEDIFF(mm, '19000101', GETDATE()) + 1, DATEADD(dd, 4, '19000101'))

Key to remember here is that 1900-01-01 is the 0 (zero) date. The DATEDIFF gets the number of month periods between now (getdate()) and 1900-01-01. If we add this value back 1900-01-01 we get the first of this month. By adding 1 to the number of periods we get the first of next month. By adding 4 days to 1900-01-01 we get 1900-01-05 and adding the number of month periods to this date gives us the 5th of the next month.

If you still have questions, let me know.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Lynn Pettis (6/8/2012)
michael vessey (6/8/2012)
try the following
declare @mydate datetime
set @mydate=GETDATE()
SELECT dateadd(dd,4,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) )
see Pinal Dave's blog post on these
http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/
edit: the other suggestions are better than mine - i did a cut an poste from t-internet
You can avoid the character conversions. Take a look at this blog post: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. You will probably find those routines quite useful.

SELECT CAST(0 AS DATETIME) -- 1900-01-01 00:00:00.000

SELECT DATEDIFF(mm, 0, GETDATE()) -- 1349 months since 1900-01-01 00:00:00.000

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) -- add 1349 months to 1900-01-01 00:00:00.000

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0) -- plus an extra month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 4) -- and 4 days

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

yes -lynn - it was a blind cut and paste from pinal dave's site (his other functions are fine, but i failed to notice the convert) - as per my edit

MVDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

michael vessey (6/8/2012)
yes -lynn - it was a blind cut and paste from pinal dave's site (his other functions are fine, but i failed to notice the convert) - as per my edit

I wouldn't know, I haven't visited his site to see what he has posted for date routines.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

michael vessey (6/8/2012)
try the following
declare @mydate datetime
set @mydate=GETDATE()
SELECT dateadd(dd,4,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) )
see Pinal Dave's blog post on these
http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/
edit: the other suggestions are better than mine - i did a cut an poste from t-internet

I took a look at the post you referenced above. I wouldn't use those routines.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 12

Lynn Pettis (6/8/2012)
dwilliscp (6/8/2012)
Ok.. I ran the statement "SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 4)
" and it does return the 5th of next month.
However I do not understand how it is doing that... by my understanding of the two commands is this:
DateDiff(datepart to compare, start, end)
DATEADD(datepart, amount to add, date)
So looking just at the DateDiff it should return 6 for 6 months crossed between 0 and June 8 2012.
So looking at the above statement we are changing the month (datepart) by the DateDiff return of 6+1 to the date of 4... see my confusion?
Maybe this will help, I'll rewrite it using dates:
SELECT DATEADD(mm, DATEDIFF(mm, '19000101', GETDATE()) + 1, DATEADD(dd, 4, '19000101'))
Key to remember here is that 1900-01-01 is the 0 (zero) date. The DATEDIFF gets the number of month periods between now (getdate()) and 1900-01-01. If we add this value back 1900-01-01 we get the first of this month. By adding 1 to the number of periods we get the first of next month. By adding 4 days to 1900-01-01 we get 1900-01-05 and adding the number of month periods to this date gives us the 5th of the next month.
If you still have questions, let me know.

Thanks that does clear things up a lot!