April 25, 2012 at 9:16 am
Now the question is: if datatype of column is datetime, then how 29thfeb2011 got inserted in the table. It is not allowed by sql server.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 25, 2012 at 9:19 am
I used redgate's data generator to populate the column with dates. The years were all random, and I wanted to go in and change all the years to either 2010 or 2011 so it will be more realistic test data. The dates that were in sql were from leap years. I was trying to update the column by just changing the year, which meant I was trying to insert 2011-02-29 which is invalid and was throwing the error. Thanks to Kingston I wont be banging my head against the desk all day trying to figure this one out!
April 25, 2012 at 9:20 am
S_Kumar_S (4/25/2012)
Now the question is: if datatype of column is datetime, then how 29thfeb2011 got inserted in the table. It is not allowed by sql server.
Your thinking wrong, take 2008-02-29, the conversion code would try and make this 2011-02-29. It isn't that 2011-02-29 already exists in the column.
April 25, 2012 at 9:25 am
If you are just using this as random dates just update your table to change the 2/29 values to something else, or delete them. Then your previous update code will probably work just fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 9:29 am
Sean Lange (4/25/2012)
If you are just using this as random dates just update your table to change the 2/29 values to something else, or delete them. Then your previous update code will probably work just fine.
Yep, that is what I did. Here is the working code:
UPDATE dbo.Test_Data
SET SERVICE_DATE =
CASE
WHEN DAY(SERVICE_DATE) = 29 AND MONTH(SERVICE_DATE) = 02
THEN '2011-02-28'
WHEN YEAR(SERVICE_DATE) < 1975
THEN '2010-'+RIGHT(LEFT(convert(VARCHAR(50),SERVICE_DATE,121),10),5)
WHEN YEAR(SERVICE_DATE) >= 1975
THEN '2011-'+RIGHT(LEFT(convert(VARCHAR(50),SERVICE_DATE,121),10),5)
END
Thank you all for your assistance!
April 25, 2012 at 9:32 am
okay, got it. thanks....sometimes you miss to see the very obvious....
Lynn Pettis (4/25/2012)
S_Kumar_S (4/25/2012)
Now the question is: if datatype of column is datetime, then how 29thfeb2011 got inserted in the table. It is not allowed by sql server.Your thinking wrong, take 2008-02-29, the conversion code would try and make this 2011-02-29. It isn't that 2011-02-29 already exists in the column.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply