Date Question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-12 : 10:31:31

When SQL server is installed into an AS400 and a data mirror is replicated what is the best way to do the date conversion in SQL Server.

Currently SQL Server pulls the date as (CYMD)

IN IBM Land this would work
(CASE WHEN valid_date(field,"*cymd")=1 then cvtdate(field,cymd) else null end)

But I have not successfully done a date conversion for SQL Server to Convert CYMD format to DDMMYY Format. Does anyone have a conversion handy ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 16:57:52

CASE WHEN ISDATE(Field) = 1 THEN CONVERT(VARCHAR(8), Field, 1) ELSE NULL END

E 12°55'05.25"
N 56°04'39.16"

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-13 : 15:40:54

Peso, Everything comes back as Null .. Typically today would show as 1080813 and I want to convert it to 8/13/2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 00:03:37

what does the '1' in 1080813 indicate ?


declare @d varchar(7)
select @d = '1080813'
select convert(datetime, case when left(@d, 1) = '1' 
                              then '20' + right(@d, 6)
                              else '19' + right(@d, 6)
                              end, 112)

KH
[spoiler]Time is always against us[/spoiler]

ZMike
Posting Yak Master

110 Posts

Posted - 2008-08-14 : 07:45:59

It says Centry (21st) They Year, Month , Date (CYMD)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 07:48:54

CASE WHEN LEN(Field) = 7 THEN CAST('20' + RIGHT(Field, 6) AS DATETIME) ELSE CAST('19' + RIGHT(Field, 6) AS DATETIME) END

E 12°55'05.25"
N 56°04'39.16"

ZMike
Posting Yak Master

110 Posts