Unable to convert character to datetime

Question

Unable to convert character to datetime

Rando

Mr or Mrs. 500

Points: 532
More actions
December 30, 2013 at 12:54 pm

#396044

I'm unable to convert a character field to datetime in TSQL.
Column:
CounterDateTime (char24), not null)
Column Example Value:
2013-12-30 12:09:00.123
Code:
SELECT
CONVERT(VARCHAR(10),CONVERT(varchar,CounterDateTime),101) as Date,
CONVERT(VARCHAR(5), CounterDateTime, 108) + ' ' + SUBSTRING(CONVERT(VARCHAR(19), CounterDateTime, 100),18,2) as Time
FROM table
Desired Results:
Date Column: 12/30/2013
Time Column: 12:09 AM
The date column comes out as 2013-12-30 which isn't a big deal but the Time column comes out as 2013- 00 and isn't working at all.
This exact code works on another table where the field is formatted as varchar. I tried converting to varchar first but that didn't work either.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Lowell SSC Guru Points: 323522 More actions · Answer 1

Rando (12/30/2013)
I'm unable to convert a character field to datetime in TSQL.
Column:
CounterDateTime (char24), not null)
Column Example Value:
2013-12-30 12:09:00.123
Code:
SELECT
CONVERT(VARCHAR(10),CONVERT(varchar,CounterDateTime),101) as Date,
CONVERT(VARCHAR(5), CounterDateTime, 108) + ' ' + SUBSTRING(CONVERT(VARCHAR(19), CounterDateTime, 100),18,2) as Time
FROM table
Desired Results:
Date Column: 12/30/2013
Time Column: 12:09 AM
The date column comes out as 2013-12-30 which isn't a big deal but the Time column comes out as 2013- 00 and isn't working at all.
This exact code works on another table where the field is formatted as varchar. I tried converting to varchar first but that didn't work either.

can you change your table to store datetime in a datetime field?

then it would be a lot easier to pull the values out, without resorting to substrings.

the date and time datatypes could be used, or the convert functions with the third parameter for style;

with mySampleData(TheDate)

AS

(

select convert(datetime,'2013-12-30 12:09:00.123')

)

select

convert(date,TheDate) AS RealDate,

convert(time,TheDate,108) As RealTime,

convert(varchar,TheDate,101) AS SimpleDate,

convert(varchar,TheDate,108) As SimpleTime

FROM mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323522 More actions · Answer 2

additionally, your "time column" comes out bad because you are converting a varchar(24) to a varchar(5); effectively the same as a LEFT function.

you get the '2013-' portion of the string, and not the datetime formatted as 108 and then converted to five chars.

if you convert it to datetime before ti's processed., like in my example, you get your desired results with the CONVERT(5) if needed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

There's no need to do a previous conversion to datetime if this is a one time extraction as SQL Server will do an implicit conversion.

WITH SampleData AS(

SELECT CAST( '2013-12-30 12:09:00.123' AS CHAR(24)) AS CounterDateTime

)

SELECT

CONVERT(VARCHAR(10),CONVERT(varchar,CounterDateTime),101) as Date,

SUBSTRING(CONVERT(VARCHAR(19), CounterDateTime, 100),12,5) as Time ,

convert(date,CounterDateTime) AS RealDate,

convert(time,CounterDateTime) As RealTime

FROM SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rando Mr or Mrs. 500 Points: 532 More actions · Answer 4

Thanks guys, this works but is it not the desired output format.

I can't change the table column formatting from char to datetime because it is automatically created by Microsoft Performance Monitor. I tried changing it and perfmon crashed so that's a no-go

I need the time formatting to be AM/PM because the data is going into a PivotChart and it's not possible to reformat linked data AFAIK.

Desired Results:

Date Column: 12/30/2013

Time Column: 12:09 AM

Lowell SSC Guru Points: 323522 More actions · Answer 5

if you explicitly convert it to datetime, your previous code would work:

WITH SampleData AS(

SELECT CAST( '2013-12-30 12:09:00.123' AS CHAR(24)) AS CounterDateTime

)

SELECT

CONVERT(VARCHAR(10),convert(datetime,CounterDateTime),101) as Date,

CONVERT(VARCHAR(5), convert(datetime,CounterDateTime), 108) + ' ' + SUBSTRING(CONVERT(VARCHAR(19), convert(datetime,CounterDateTime), 100),18,2) as Time

FROM SampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rando Mr or Mrs. 500 Points: 532 More actions · Answer 6

That's where I run into the syntax error;

Msg 241, Level 16, State 1, Line 1

Syntax error converting datetime from character string.

Lowell SSC Guru Points: 323522 More actions · Answer 7

Rando (12/30/2013)
That's where I run into the syntax error;
Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

can you do a select to find non-convertable date ranges?

ie

SELECT * FROM YOURTABLE WHERE IsDAte(CounterDateTime) = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rando Mr or Mrs. 500 Points: 532 More actions · Answer 8

Rando

Mr or Mrs. 500

Points: 532

December 30, 2013 at 2:32 pm

#1676477

All records in the table came back :hehe:

ScottPletcher SSC Guru Points: 101192 More actions · Answer 9

SELECT

CONVERT(varchar(10), CAST(CounterDateTime AS datetime), 101) AS Date,

RIGHT(CONVERT(varchar(20), CAST(CounterDateTime AS datetime), 100), 7) AS Time

FROM table

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 10

SELECT CounterDateTime = CAST(CAST(CounterDateTime AS CHAR(24)) AS DATETIME)

FROM (SELECT CounterDateTime = '2013-12-30 12:09:00.123' + CHAR(10)) d

-- Msg 241, Level 16, State 1, Line 1

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

SELECT CounterDateTime = CAST(CAST(CounterDateTime AS CHAR(23)) AS DATETIME)

FROM (SELECT CounterDateTime = '2013-12-30 12:09:00.123' + CHAR(10)) d

-- 2013-12-30 12:09:00.123

^{“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

Rando Mr or Mrs. 500 Points: 532 More actions · Answer 11

Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...

Again, I'm just looking for the results to be the following;

Date: 12/30/2013

Time: 01:00 PM etc

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

Rando (1/2/2014)
Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...
Again, I'm just looking for the results to be the following;
Date: 12/30/2013
Time: 01:00 PM etc

Surely you know what to do with a date field?

^{“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

Rando Mr or Mrs. 500 Points: 532 More actions · Answer 13

ChrisM@Work (1/2/2014)
Rando (1/2/2014)
Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...
Again, I'm just looking for the results to be the following;
Date: 12/30/2013
Time: 01:00 PM etc
Surely you know what to do with a date field?

Ive only figured out the date field, I'm having issues with time

CONVERT(CHAR,CAST(CAST(CounterDateTime AS CHAR(10)) AS DATETIME),101) AS Date,

SUBSTRING(CONVERT(CHAR, CAST(CAST(CounterDateTime AS CHAR(10)) AS DATETIME), 100),12,6) + ' ' + SUBSTRING(CONVERT(CHAR, CAST(CAST(CounterDateTime AS CHAR(10)) AS DATETIME), 100),18,2) as Time

All my times are coming back as 12:00 AM instead of the actual time

ScottPletcher SSC Guru Points: 101192 More actions · Answer 14

Rando (1/2/2014)
Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...
Again, I'm just looking for the results to be the following;
Date: 12/30/2013
Time: 01:00 PM etc

Did you Scott's (my) original code? Or a version of it as modified by someone else?

If your local date format is dmy instead of mdy, you'll need to specify:

SET DATEFORMAT dmy

before converting values in the format 'YYYY-MM-DD' to a date/datetime, but that will be true for any method of converting.

If that's not the problem, then you have bad values in the date column, and you'll need to handle those separately.

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".

Unable to convert character to datetime

Cookies on SQLServerCentral