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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy