Integer to datetime t-SQL

Question

I would like to convert int to date format, The values are in this format ddmmyy 280118

select cast( '280118' as date) this results in 2028-01-18,

I have found way from Stack using substring

select convert(date, SUBSTRING('280118',1,2)+'/'+
         SUBSTRING('280118',3,2)+'/'+SUBSTRING('280118',5,2),3)

Expected Result 2018-01-28. Is there any simple alternate way to acheive this in SQL server 2016, It is also quite useful if you can suggest from version 2012 onwards.

I have seen posts as this has been answered already many times, i would like to see any simpler ways of answering this.

Appreciate your comments, suggestions

are they all from the 21st century? Easier way would be to store dates as dates. — S3S, Apr 11 '18 at 14:59
The secret to these questions is to google for the mask: *t-sql "ddmmyy" to datetime* — Alex K., Apr 11 '18 at 15:01
I dont think we go beyong 20th century, but i am really looking for a simple solution. This data stored in staging in this format, so would like to store it in correct format in actual table @scsimon — Hadrian, Apr 11 '18 at 15:02
Did we learn nothing from Y2K? Is it possible to fix the source of this data to use 4 digit years? — Sean Lange, Apr 11 '18 at 15:02
Well, I was more so interested if there were any 19XX years. I suspect there would be, and that's going to be fun for you to work with. I agree with @SeanLange though, naturally — S3S, Apr 11 '18 at 15:03
I think this is similar to the question answered here: [link](https://stackoverflow.com/questions/13512019/sql-convert-ddmmyy-to-datetime?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) by @bummi — Michael, Apr 11 '18 at 15:04
@SeanLange Source cant not be alterted, Source column comes as Schemenumber(ex01)+ddmmyy+mmss+Storenumber, I am modifying traditional strign type of workign approach to align correct data types — Hadrian, Apr 11 '18 at 15:06
@Michael I am looking for a simple answer to `select convert (datetime, Stuff(Stuff('311012',5,0,'.'),3,0,'.'), 4)` as you have suggested in the link — Hadrian, Apr 11 '18 at 15:07
Cant understand why question gets downvote as it is clear and concise, i would appreciate any comments made to improve question or out of topic — Hadrian, Apr 11 '18 at 15:11
Well somebody should go smack however decided to go back to 2 digit years. — Sean Lange, Apr 11 '18 at 15:13
Seems that downvoting is an epidemic around here. People like to downvote a lot. Not really sure why. — Sean Lange, Apr 11 '18 at 15:14
@SeanLange If i ask Business managers or IT Team how data is handled in the past ,the answer i get `it is what it is` :) — Hadrian, Apr 11 '18 at 15:14
@SeanLange I believe it doesnt motive to post questions, but i have seen knowledge base in stack with SQL i am ok with it — Hadrian, Apr 11 '18 at 15:17
I agree. It is a demotivator for people to ask questions. Sure there are plenty of repeats and those with no research. Yours has certainly been answered before but you did at least attempt to solve it on your own. Hardly seems downvote worthy to me. — Sean Lange, Apr 11 '18 at 15:19
Possible duplicate of [SQL convert 'DDMMYY' to datetime](https://stackoverflow.com/questions/13512019/sql-convert-ddmmyy-to-datetime) — underscore_d, Apr 11 '18 at 15:19

score 1 · Accepted Answer · answered Apr 11 '18 at 15:17

1

You can use stuff here and the format option of convert to do this fairly easily. This of course makes the assumption that ALL dates will use the default century. In a couple decades this will no longer get the correct decade unless MS changes their default logic for centuries with 2 digit years.

declare @YourDate varchar(10) = '280118'

select convert(date, stuff(stuff(@YourDate, 3, 0, '/'), 6, 0, '/'), 3)

answered Apr 11 '18 at 15:17

Sean Lange

33,028
3
25
40

Appreciate this answer, i have used this answer in my comments. I guess there is no other way round than using stuff/substring .Like `FORMAT`? – Hadrian Apr 11 '18 at 15:20
There are plenty of alternatives but none as simple that I am aware of. – Sean Lange Apr 11 '18 at 15:21
`Format` returns a string, not converting from string to another data type – Zohar Peled Apr 11 '18 at 15:21
@ZoharPeled I guess there is no other ways, the only reason i have asked this question if any new functions added to the newer versions. – Hadrian Apr 11 '18 at 15:24
Thanks Sean, I would be happier if some other users make an effort to find an alternative solution rather commenting, or marking duplicate. – Hadrian Apr 11 '18 at 15:28
1

Side note: If you are only ever going to work with dates in this century, change the outer stuff to insert `/20` instead of `/`, and convert using 103 style. That way, you don't have to worry about possible 20th century dates: `select convert(date, stuff(stuff(@YourDate, 3, 0, '/'), 6, 0, '/20'), 103)` – Zohar Peled Apr 11 '18 at 15:36

Integer to datetime t-SQL

1 Answers1