Datetime conversion - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Datetime conversion

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2010-08-10 : 11:56:04

When I execute the below mentioned function the year less than 50 has updated with 20th century and the year greater than 50 has updated as 19th century.

Query: select convert(datetime,'01/07/10',11)
output: 2001-07-10 00:00:00.000

Query: select convert(datetime,'50/07/10',11)
output: 1950-07-10 00:00:00.000

I want to update all the years as 19th century. please help to overcome this.

thanks in advance
by
subha

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-10 : 12:32:20

A workaround way:

Declare @val varchar(50)
set @val = '50/07/10'

Select Case when year(convert(datetime,@Val,11)) >= 2000 then dateadd(yy, -100, convert(datetime,@val,11))
else convert(datetime,@Val,11)
end

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-11 : 04:54:32

or

Declare @val varchar(50)
set @val = '81/07/10'
select convert(datetime,'19'+replace(@Val,'/',''),112)

Madhivanan

Failing to plan is Planning to fail

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-11 : 05:08:02

or

Declare @val varchar(50)
set @val = '50/07/10'
SELECT dateadd(yy,-100,convert(datetime,@Val,11))

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-14 : 00:52:11

quote:
Originally posted by Devart

or

Declare @val varchar(50)
set @val = '50/07/10'
SELECT dateadd(yy,-100,convert(datetime,@Val,11))

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

Hi Devart,

The approach will give incorrect result if the date belongs to 20th century example : 1980-07-01

Declare @val varchar(50)
set @val = '80/07/10'
SELECT dateadd(yy,-100,convert(datetime,@Val,11))

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-01 : 02:54:35

Hi pk_bohra,

Sorry for the delayed answer.
If only my memory does not fail me, 19th century embraces 1801-1900 period. The user wanted to get 19th century. The mentioned script restores the data within 19th century if the year is greater than 50.

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 03:03:01

[code]DECLARE @Sample TABLE
(
dt VARCHAR(20) NOT NULL
)

INSERT @Sample
SELECT '01/07/10' UNION ALL
SELECT '50/07/10'

-- Peso 1
SELECT dt,
CONVERT(DATETIME, dt, 11) AS Original,
CONVERT(DATETIME, '19' + dt, 111) AS Peso
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources