June 12, 2013 at 8:19 am
I have dates stored on an AS400 platform in the following format that I need to convert to a standard date in SQl so I can use datediff in my select statements.
Current Format: CYYMMDD example: 1130531 = 05/31/2013 in this format, the C is either 0 (for 1900) or 1 (for 2000)
Desired Format: 05/31/2013
All the articles I have found in the forums deal with a properly formated set of digits. Any help would be greatly appreciated!
June 12, 2013 at 8:35 am
Something like this should help you get started.
set dateformat ymd
select case left(AS400, 1)
when 1 then cast(RIGHT(AS400, 6) as datetime)
when 0 then DATEADD(year, -100, cast(RIGHT(AS400, 6) as datetime))
end as CalcDate
from (Values('1130531'), ('0130531')) x(AS400)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2013 at 8:37 am
SELECT
CYYMMDD,
SQLDateTime = CONVERT(DATETIME,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112)
FROM (SELECT CYYMMDD = '1130531') t
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2013 at 8:39 am
Not especially elegant, but this should work:-
DECLARE @datestring char(9)
SET @Datestring = '1130531'
SELECT SUBSTRING(@datestring, 4, 2) AS MonthPart
, SUBSTRING(@dateString, 6,2) AS DayPart
, CASE WHEN LEFT(@datestring, 1) = 0 THEN '19' ELSE '20' END + SUBSTRING(@datestring, 2,2) AS YearPart
, CONVERT(date, CASE WHEN LEFT(@datestring, 1) = 0 THEN '19' ELSE '20' END + SUBSTRING(@datestring, 2,2) + SUBSTRING(@datestring, 4, 2) + SUBSTRING(@dateString, 6,2), 112) AS ConvertedDate
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 12, 2013 at 9:09 am
Just another way to do it:
code="sql"]
declare @AS400Date char(7) = '1130531';
select cast(cast(cast(@AS400Date as int) + 19000000 as char(8)) as date);
[/code]
June 12, 2013 at 9:22 am
SELECT
CYYMMDD,
SQLDate1 = CONVERT(DATE,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112),
SQLDate2 = cast(cast(cast(CYYMMDD as int) + 19000000 as char(8)) as date),
SQLDate3 = CONVERT(DATE,CAST('1130531' + 19000000 AS CHAR(8)),112),
SQLDate4 = CONVERT(DATE,RIGHT(CYYMMDD,6),12),
ConvertedDate = CONVERT(date,
CASE WHEN LEFT(CYYMMDD, 1) = 0 THEN '19' ELSE '20' END
+ SUBSTRING(CYYMMDD, 2,2)
+ SUBSTRING(CYYMMDD, 4,2)
+ SUBSTRING(CYYMMDD, 6,2), 112)
FROM (SELECT CYYMMDD = '1130531') t
Any more?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2013 at 11:19 am
Thank you all for the quick responses!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply