T-SQL: How to convert string representations of a date

Question

I've got some dates stored in varchar(10) format.

Dates are like so:

I would like them in dd/mm/yyyy format (also a DATE datatype)

I have tried the following:

SELECT CONVERT(VARCHAR(max), startWeek, 103) date
FROM dbo.GoogledataFinal

Why does this output:

Would be great if you could fix my code, but I'm more interested in WHY this behaviour is happening, so explaining like I'm 5 years old might be helpful.

Andomar · Accepted Answer · 2012-10-31 11:31:44Z

5

You are converting from varchar to varchar. The style parameter is not even used for that.

Instead, convert to date, and then back to varchar:

select convert(varchar(10), cast('2008-01-06' as datetime), 103)

This prints:

06/01/2008

answered Oct 31, 2012 at 11:31

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Waller Over a year ago

Many thanks, I always hated working with dates in SQL... Wondered what the differences with cast and convert where but this helps. Problem is fixed. Thanks.

Daniel Reis Over a year ago

@waller convert allows you to pass a formatting mask, which is important to handle strings representing dates.

Dennis Traub · Accepted Answer · 2012-10-31 11:31:19Z

2

SELECT CONVERT(varchar(10), CONVERT(datetime, startWeek), 103) date
FROM dbo.GoogledataFinal

answered Oct 31, 2012 at 11:31

Dennis Traub

51.9k7 gold badges100 silver badges113 bronze badges

1 Comment

Taryn Over a year ago

you should really include a length on your varchar

Kapil Khandelwal · Accepted Answer · 2012-10-31 11:36:04Z

0

Try this:

SELECT (SUBSTRING (startWeek,9, 2) + '/' + SUBSTRING (startWeek,6, 2) + '/' SUBSTRING (startWeek,1, 4))  AS date
FROM dbo.GoogledataFinal

answered Oct 31, 2012 at 11:36

Kapil Khandelwal

16.2k3 gold badges49 silver badges55 bronze badges

Comments

Daniel Reis · Accepted Answer · 2012-10-31 12:41:18Z

0

You need to convert the varchar to a datetime, and then back to varchar using the desired format:

SELECT CONVERT( varchar(10), 
         CONVERT(DATETIME, '2008-02-03', 120) -- Varchar "yyyy-mm-dd" to Datetime
       , 103) -- Datetime to Varchar "dd/mm/yyyy"

For your case the SQL might be:

SELECT CONVERT( varchar(10), 
         CONVERT(DATETIME, startWeek, 120)
       , 103)
FROM dbo.GoogledataFinal

edited Oct 31, 2012 at 12:41

answered Oct 31, 2012 at 11:38

Daniel Reis

13.5k6 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

T-SQL: How to convert string representations of a date

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related