syntax stumped
-
Hi,
Think I'm overlooking the obvious I have this statement:
select *, '20' as [Yrs of Serv]from cte where [Due Date for 20 years of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP + 90),'19000101')
I need to run it for April like this:
select *, '20' as [Yrs of Serv]from cte where [Due Date for 20 years of Service Award] >= dateadd(month, datediff(month,'19000101','20130401' + 90),'19000101')
But when I change out the 'CURRENT_TIMESTAMP' I get "Arithmetic overflow error converting expression to data type datetime"
What am I doing wrong?
-
i think it's the +90 INSIDE the datediff: '20130401' + 90 probably turns into either 20130491 or '2013040190', either of which are not valid datetimes.
maybe like this?
select *, '20' as [Yrs of Serv]from cte
where [Due Date for 20 years of Service Award]
>= dateadd(
month,
datediff(month,'19000101','20130401') + 90 ,
'19000101')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Try changing
'20130401' + 90
to
CAST('20130401' AS DATETIME) + 90
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Using the "+X" shorthand way of adding days to a date is not the best practice, and in this case it is the cause of your error. It is always preferable to use DATEADD().
dateadd(month, datediff(month,'19000101', dateadd(day, 90, '20130401')),'19000101') -
Awesome thanks guys! Much appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply