how to update datetime like this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to update datetime like this?

Author

Topic

saxford
Starting Member

7 Posts

Posted - 2006-10-07 : 05:54:19

i have a problem
my datetime field is 23/12/1999
and i need to change only year field
like this 23/12/2542
i have 100k row
how to do this plz give me some advice

Kristen
Test

22859 Posts

Posted - 2006-10-07 : 05:56:34

You can use

DATEADD(Year, 2542-1999, MyDateColumn)

What do you want to happen to 29-Feb dates if the new year is not a leap year?

Kristen

saxford
Starting Member

7 Posts

Posted - 2006-10-07 : 06:47:24

this is my sql

UPDATE historyemp
SET hiredate = DATEADD(Year,2542+1999,historyemp.hiredate);

it won't work
or my syntax is worng

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-07 : 07:51:49

quote:
Originally posted by saxford

this is my sql

UPDATE historyemp
SET hiredate = DATEADD(Year,2542+1999,historyemp.hiredate);

it won't work
or my syntax is worng


PDATE historyemp
SET hiredate = DATEADD(Year,2542-1999,historyemp.hiredate)

Chirag

saxford
Starting Member

7 Posts

Posted - 2006-10-07 : 08:05:23

That work Thank you
and i applied it to this

UPDATE historyemp
SET hiredate = DATEADD(year,+543,hiredate)
WHERE emdid!=''

thank a lot

Kristen
Test

22859 Posts

Posted - 2006-10-08 : 07:42:46

Did you review what would/has happened to 29-Feb dates?

Kristen

saxford
Starting Member

7 Posts

Posted - 2006-10-08 : 08:05:49

quote:
Originally posted by Kristen

Did you review what would/has happened to 29-Feb dates?

Kristen

i am lucky
all of my Db
there are no 29-FEB
That is it

Kristen
Test

22859 Posts

Posted - 2006-10-08 : 08:14:10

Fair enough!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-09 : 10:00:02

quote:
Originally posted by saxford

this is my sql

UPDATE historyemp
SET hiredate = DATEADD(Year,2542+1999,historyemp.hiredate);

it won't work
or my syntax is worng

Because you dint read Kristen's first reply properly

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-09 : 15:25:44

1999 to 2542?

Are you converting from the Gregorian calendar to the Lao calendar?
http://www.laoembassy.com/news/MarApril99.htm#8
"On April 24,1999 at the Lao embassy in Washington D.C. over 600 guests from as far as Canada, California and other states in the United States joined the celebration of the Lao New Year 2542."

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources