CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
mohan111
mohan111

Reputation: 8865

Conversion issues with VARCHAR to DATE

I have sample data like this : 

SELECT CONVERT(CHAR(19), CONVERT(DATE, '11/10/1997', 3), 120);

when i execute this I'm getting Error like this :

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

So then i have removed first 2 characters of year 1997 

SELECT CONVERT(CHAR(19), CONVERT(DATE, '11/10/97', 3), 120);

And it gave Result Set like this : 

Date
1997-10-11

So it got converted from DD/MM/YY Format to YYYY-MM-DD

Fine But why it has taken 1997 if I'm going to give 17 it will give as 2017. If i give 37 it will give 2037 and if i give 67 and it is giving 1967 .

And lastly how can DD/MM/YYYY to YYYY-MM-DD Format

Upvotes: 1

Views: 624

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272006

You need to use 103 when you specify four digit year:

SELECT CONVERT(DATE, '11/10/1997', 103)
-- 1997-10-11 (DATE)

If you want to convert the date back to a string, convert it again:

SELECT CONVERT(VARCHAR(10), CONVERT(DATE, '11/10/1997', 103), 120)
-- 1997-10-11 (VARCHAR)

Complete list of styles is available here.

Upvotes: 1

user6325732
user6325732

Reputation: 23

Can you try this?

declare @datechar as char(19) ='11/10/1997'

select FORMAT(convert(date,@datechar,103),'yyyy-MM-dd')

Upvotes: 0

cenzuratu
cenzuratu

Reputation: 51

Instead of 3 you can pun 103. So : SELECT CONVERT(CHAR(19), CONVERT(DATE, '11/10/1997', 103), 120); will return 1997-10-11

Upvotes: 0

Related Questions