Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to update just the year in date fields (non datetime data type) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, May 18, 2012 11:53 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, August 29, 2012 7:08 PM Points: 8, Visits: 62
 Hi Guys,I have to write a script to update ALL date fields in the database my company's software uses. This is mainly an exercise to help me learn SQL (I am encouraged to use all resources). Fortunately, most of the date fields are in datetime, but I am having a hard time figuring out what to do with the varchar/bigint/nvarchar ones such as 2004-06-23, 1276449969378, May 20 2, 06/14/2011. I have to update all fields one year ahead, but leave the rest of the date unchanged. Can anyone please point me in the right direction of where to start?Thanks :)
Post #1302733
 Posted Friday, May 18, 2012 1:25 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:40 PM Points: 5,328, Visits: 21,961
 Here is a few to get you started.`DECLARE @d1 VARCHAR(20)DECLARE @d2 NVARCHAR(20)DECLARE @d3 VARCHAR(20)DECLARE @d4 NVARCHAR(20)DECLARE @d5 VARCHAR(20)SET @d1 = '2004-06-23'SET @d2 = '2004-06-23'SET @d3 = '06/14/2011'SET @d4 = '06/14/2011'SET @d5 = 'May 20 2011'SET @d1 = DATEADD(YY,1,@d1)SET @d2 = DATEADD(YY,1,@d2)SET @d3 = DATEADD(YY,1,@d3)SET @d4 = DATEADD(YY,1,@d4)SELECT convert(VARCHAR(20),CAST(@d1 AS DATETIME),101) AS 'Cast of d1'SELECT convert(VARCHAR(20),CAST(@d2 AS DATETIME),101) AS 'Cast of d2'SELECT convert(VARCHAR(20),CAST(@d3 AS DATETIME),101) AS 'Cast of d3'SELECT convert(VARCHAR(20),CAST(@d4 AS DATETIME),101) AS 'Cast of d4'Results:Cast of d1--------------------06/23/2005Cast of d2--------------------06/23/2005Cast of d3--------------------06/14/2012Cast of d4--------------------06/14/2012` If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1302793
 Posted Friday, May 18, 2012 1:37 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:40 PM Points: 5,328, Visits: 21,961
 Had to reduce the value of you BIGINT (other wise - overflow error)but here goes`DECLARE @B BIGINTSET @B = 922337SELECT convert(VARCHAR(20),CAST(@B AS DATETIME),101) `Result: 04/10/4425 If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1302804
 Posted Friday, May 18, 2012 2:26 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, August 29, 2012 7:08 PM Points: 8, Visits: 62
 Thank you so much!It is going to take me a little bit to translate what you wrote into english (I am VERY new to this), but I will try out your suggestions
Post #1302833
 Posted Friday, May 18, 2012 2:57 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:40 PM Points: 5,328, Visits: 21,961
 Now I did a little testing with the BIGINT value to deteremine the maximum BIGINT value that could be converted to DATETIME as follows:`DECLARE @B BIGINTSET @B = 2958457 WHILE @B < = 2958464 BEGIN SELECT convert(VARCHAR(20),CAST(@B AS DATETIME),101),@B SET @B = @B + 1 END`The last value BEFORE the error message was: 2958463 In your code if you will be attempting to convert a BIGINT value to a date use something like :`SELECT convert(VARCHAR(20),CAST(@B AS DATETIME),101) WHERE @B < 2958463`In order to clarify or confuse here are the result of additional code.`DECLARE @B BIGINTSET @B = 41046 SELECT @B AS 'Bigint',CAST(@B AS BINARY(8)) AS 'Binary 8',convert(VARCHAR(20) ,CAST(@B AS DATETIME),101) AS 'Date'SELECT CAST(GETDATE() AS BIGINT) AS 'Bigint',CAST(GETDATE() AS BINARY(8)) AS 'Binary 8',GETDATE() AS 'Date'Result:Bigint Binary 8 Date41046 0x000000000000A056 05/19/201241046 0x0000A0560090F582 2012-05-19 08:47:46.780`To clarify read more:http://www.sql-server-performance.com/2004/datetime-datatype/ If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1302851

 Permissions