May 18, 2012 at 11:53 am
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 🙂
May 18, 2012 at 1:25 pm
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/2005
Cast of d2
--------------------
06/23/2005
Cast of d3
--------------------
06/14/2012
Cast of d4
--------------------
06/14/2012
May 18, 2012 at 2:26 pm
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 🙂
May 18, 2012 at 2:57 pm
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-2 BIGINT
SET @b-2 = 2958457
WHILE @b-2 < = 2958464
BEGIN
SELECT convert(VARCHAR(20),CAST(@B AS DATETIME),101),@B
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-2 < 2958463
In order to clarify or confuse here are the result of additional code.
DECLARE @b-2 BIGINT
SET @b-2 = 41046
SELECT @b-2 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 Date
41046 0x000000000000A056 05/19/2012
41046 0x0000A0560090F582 2012-05-19 08:47:46.780
To clarify read more:
http://www.sql-server-performance.com/2004/datetime-datatype/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply