Character string conversion to date type. char(8) to Date ( from char(8) (23022016) to date(dd/mm/yyyy) (23/02/2016)

Question

Character string conversion to date type. char(8) to Date ( from char(8) (23022016) to date(dd/mm/yyyy) (23/02/2016)

GKR 52789

SSC Enthusiast

Points: 199
More actions
February 23, 2016 at 1:38 am

#311602

Hello i have problem with conversion from string to date type:
In one column i have Date data like 23:02:2016, i try replace to 23022016 format, after that i try to convert to date type, but i cant i try with convert and cast, but always got error .
I try show in pictures.
SELECT CONVERT(date, Date1, 103) FROM #Peakoftheday

Viewing 7 posts - 1 through 6 (of 6 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:

CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2) + LEFT(Date1,2), 112)

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

spaghettidba (2/23/2016)
There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:
CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2) + LEFT(Date1,2), 112)

Or the British version:

SELECT CONVERT(DATE,REPLACE('23:02:2016',':','/'),103) -- dd/mm/yyyy (British/French)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Ah, yes, I forgot about that option. Nice catch Chris.

-- Gianluca Sartori

GKR 52789 SSC Enthusiast Points: 199 More actions · Answer 4

GKR 52789

SSC Enthusiast

Points: 199

February 23, 2016 at 2:30 am

#1860313

Thank you.... :blush:

ScottPletcher SSC Guru Points: 101134 More actions · Answer 5

ChrisM@Work (2/23/2016)
spaghettidba (2/23/2016)
There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:
CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2) + LEFT(Date1,2), 112)
Or the British version:
SELECT CONVERT(DATE,REPLACE('23:02:2016',':','/'),103) -- dd/mm/yyyy (British/French)

Don't actually need the REPLACE: SQL will also accept colons as delimiters in that format.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

ScottPletcher (2/23/2016)
ChrisM@Work (2/23/2016)
spaghettidba (2/23/2016)
There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:
CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2) + LEFT(Date1,2), 112)
Or the British version:
SELECT CONVERT(DATE,REPLACE('23:02:2016',':','/'),103) -- dd/mm/yyyy (British/French)
Don't actually need the REPLACE: SQL will also accept colons as delimiters in that format.

Not in Britain, where we use this style code a lot:

SELECT CONVERT(DATE,'23:02:2016',103) -- dd/mm/yyyy (British/French)

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden