Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update just the year in date fields (non datetime data type) Expand / Collapse
Author
Message
Posted Friday, May 18, 2012 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 5,571, Visits: 24,770
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




If everything seems to be going well, you have obviously overlooked something.

Ron

Please 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 5,571, Visits: 24,770
Had to reduce the value of you BIGINT (other wise - overflow error)
but here goes
DECLARE @B BIGINT
SET @B = 922337
SELECT convert(VARCHAR(20),CAST(@B AS DATETIME),101)

Result: 04/10/4425


If everything seems to be going well, you have obviously overlooked something.

Ron

Please 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 5,571, Visits: 24,770
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 BIGINT
SET @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 BIGINT
SET @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 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/


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1302851
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse