Convert varchar to datetime uk english format

Question

Post reply

Convert varchar to datetime uk english format

robert.morgan 63781

SSC-Addicted

Points: 438
More actions
June 8, 2017 at 3:35 pm

#402151

I have a column that is varchar(8) which holds a date as 20170609. I need to convert it to datetime in the format dd/mm/yyyy 09/06/2017

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Try:
SELECT Convert(varchar(10),Cast('20170609' AS date), 103) Char8AsUKdate

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 2

CAST and CONVERT - https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

robert.morgan 63781 SSC-Addicted Points: 438 More actions · Answer 3

robert.morgan 63781

SSC-Addicted

Points: 438

June 8, 2017 at 4:47 pm

#1946091

Thanks that is spot on.

How do I mark as answered?

Joe Torre SSChampion Points: 10248 More actions · Answer 4

Joe Torre

SSChampion

Points: 10248

June 8, 2017 at 5:40 pm

#1946093

Like it.

robert.morgan 63781 SSC-Addicted Points: 438 More actions · Answer 5

The like only appears on the last post. How do I like a previous post?

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 6

robert.morgan 63781 - Thursday, June 8, 2017 5:53 PM
The like only appears on the last post. How do I like a previous post?

Thumbs up below each post.

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

It would be remiss if somebody didn't mention that the root of your issue is that you are storing dates as varchar. You should choose the appropriate datatype for the data being stored. If this was a date datatype instead this would not even be a question you would have needed to ask. You would simply retrieve the data and put your formatting in the front end where it belongs.

_______________________________________________________________

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/

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 8

Sean Lange - Friday, June 9, 2017 8:49 AM
It would be remiss if somebody didn't mention that the root of your issue is that you are storing dates as varchar. You should choose the appropriate datatype for the data being stored. If this was a date datatype instead this would not even be a question you would have needed to ask. You would simply retrieve the data and put your formatting in the front end where it belongs.

Liked it. 🙂

robert.morgan 63781 SSC-Addicted Points: 438 More actions · Answer 9

Not an option as this is an application database, as such we have no control over how they design their database.
We extract data for reporting purposes only.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

robert.morgan 63781 - Sunday, June 11, 2017 3:04 PM
Not an option as this is an application database, as such we have no control over how they design their database.
We extract data for reporting purposes only.

Are you querying the data in place or populating other tables with the data for reporting? If you have your own tables for reporting, then you can design it well. Your report users will thank you for it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 11

Considering that you don't have the option to make this an actual DATE data type, I would suggest that you leave the data in it's current format. The YYYYMMDD is a universal format that can be correctly translated into a date no matter what collation settings are being used.
If this is just for display purposes in a SELECT statement, you can use something like the following...
DECLARE @StringDate CHAR(8) = '20170609';

SELECT UK_Format = CONCAT(sv.DY, '/', sv.MO, '/', sv.YR) FROM ( VALUES ( SUBSTRING(@StringDate, 1, 4), SUBSTRING(@StringDate, 5, 2), SUBSTRING(@StringDate, 7, 2) ) ) sv (YR, MO, DY);

-- OR --

SELECT UK_Format = CONCAT(SUBSTRING(@StringDate, 7, 2), '/', SUBSTRING(@StringDate, 5, 2), '/', SUBSTRING(@StringDate, 1, 4));