SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to update just the year in date fields (non datetime data type)


How to update just the year in date fields (non datetime data type)

Author
Message
nic79
nic79
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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 Smile
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7813 Visits: 25280
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7813 Visits: 25280
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
nic79
nic79
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 62
Thank you so much!

It is going to take me a little bit to translate what you wrote into english :-D (I am VERY new to this), but I will try out your suggestions :-)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7813 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search