SQL convert 'DDMMYY' to datetime

Question

I am trying to convert a date from DDMMYY format to datetime in SQL Server.

I am using the convert command as follows

select convert (datetime, '311012', <style>)

I have tried looking on msdn for the supported styles but haven't found an exact match.

Help will be much appreciated.

Do you mean you have a _string_, representing a date, which is formatted as `DDMMYY`? — Oded, Nov 22 '12 at 11:42
A very nice Explanation:- http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion — Pranav, Nov 22 '12 at 11:45
Yes I have a string with no spaces in DDMMYY format. For eg - '311012' — dopplesoldner, Nov 22 '12 at 11:45
If this is a string literal, How did you know whether `YY` is `20yy` or `19yy`? in your example is it `2012` or `1912`? — Mahmoud Gamal, Nov 22 '12 at 11:49
@Pranav I did look at that explanation as well as the MSDN page - but there is no example for my exact format DDMMYY — dopplesoldner, Nov 22 '12 at 11:49
In my example it is 2012 - that is provided in the source file specifications — dopplesoldner, Nov 22 '12 at 11:50

score 12 · Accepted Answer · answered Nov 22 '12 at 11:53

12

select convert (datetime,  Stuff(Stuff('311012',5,0,'.'),3,0,'.'), 4)

answered Nov 22 '12 at 11:53

bummi

26,839
13
60
97

"Stuff" inserst a string in another sting. In this example, "Stuff" is used twice to insert two periods (at index 5 (fist) and 3 (last)). Thus transforming "311012" to "31.10.12" which is a datetime format (4) that convert can understand. – Andreas Jan 18 '17 at 12:21

Pranav · Answer 2 · 2012-11-22T12:15:14.423

0

Try like this :-

declare  @val1 varchar(30)
select @val1=SUBSTRING('311012',1 ,2)+'/'+SUBSTRING('311012',3 ,2)+'/'+'20'+SUBSTRING('311012',5 ,2)
SELECT CONVERT(datetime,@val1,103)

edited Nov 22 '12 at 12:15

answered Nov 22 '12 at 11:54

Pranav

8,013
4
24
41

score -2 · Answer 3 · answered Nov 22 '12 at 12:58

-2

select CAST('121031' AS datetime)  as d -- For YYMMDD

answered Nov 22 '12 at 12:58

Ranjith J

107
1
6

SQL convert 'DDMMYY' to datetime

3 Answers3

Linked