December 16, 2015 at 12:05 pm
I'm trying to grab the first day of the month from 4 months ago and where I'd see a problem with it is January through April.
My goal is, when running the script any day this month (December), I need it to use the date 2015-08-01. In January 2016, I need it to use the date 2015-09-01. February 2016 needs to produce 2015-10-01 and so on.
Here is what I have so far...
,two = CASE DatePart(mm,'2016-01-01') - 4
WHEN 0 THEN (DATEPART(yyyy,GETDATE())) + '-12-01'
WHEN -1 THEN (DATEPART(yyyy,GETDATE()) - 1) + '-11-01'
WHEN -2 THEN (DATEPART(yyyy,GETDATE()) - 1) + '-10-01'
WHEN -3 THEN (DATEPART(yyyy,GETDATE()) - 1) + '-09-01'
ELSE (DATEPART(yyyy,GETDATE())) + '-' + DATEPART(mm,GETDATE()) - 4 + '-' + DatePart(dd,getdate())
END
(I'm sticking 2016-01-01 in the first line as a test. Once I get past my error, I'll use GETDATE())
When using this code, I'm getting this error...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-09-01' to data type int.
When I changed from...
WHEN -3 THEN (DATEPART(yyyy,GETDATE()) - 1) + '-09-01'
...to this...
WHEN -3 THEN CAST((DATEPART(yyyy,GETDATE()) - 1) AS CHAR(4)) + '-09-01'
...I get this error...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2014-09-01' to data type int.
Please disregard the year in the error message. I know why it's producing 2014.
What am I missing to get around the error?
TIA,
John
December 16, 2015 at 12:18 pm
Try this:
select DATEADD(mm,-4,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 16, 2015 at 1:22 pm
Phil's solution is a nice way of doing what you're wanting to do.
You were encountering the error because the data type returned by a CASE is the data type with the highest precedence among the possible result expressions (https://msdn.microsoft.com/en-us/library/ms181765.aspx).
In this case, you only changed the data type returned by the result for the WHEN -3 condition. The other result expressions were all still of the integer data type, and since integer has a higher precedence than CHAR, SQL Server was attempting to convert the string '2014-09-01' to INT, which will fail.
If you had cast all the potential result expressions as CHAR, then you would avoid the error.
Phil's solution is the better way of dealing with this anyway, but issues arising from mixing data types in the result expressions of a CASE statement are common enough I thought the reason for the error in the original code was worth mentioning.
Cheers!
December 16, 2015 at 1:32 pm
Not only will Phil's approach keep dates as dates, but I'd bet it's a whole lot faster as well.
December 16, 2015 at 1:57 pm
Phil Parkin (12/16/2015)
Try this:
select DATEADD(mm,-4,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))
Awesome thanks!
December 16, 2015 at 1:59 pm
@Ed and Jacob
Thanks for your responses. I love explanations as why things work and I'm all for making scripts run faster.
Thanks again.
John
December 16, 2015 at 2:10 pm
J M-314995 (12/16/2015)
@Ed and JacobThanks for your responses. I love explanations as why things work and I'm all for making scripts run faster.
Thanks again.
John
If you work with dates and want some great routines, Lynn Pettis has an page that contains a lot of very useful ones.
http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply