Unable to convert character to datetime

Question

Post reply

Unable to convert character to datetime

Viewing 8 posts - 16 through 22 (of 22 total)

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

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

I'm getting closer. Now my only problem is the time field is displaying in 24h instead of 12h. Any ideas?;

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

SELECT

x.CounterDateTime, -- stare & compare

[Date] = CONVERT(VARCHAR,x.CounterDateTime,101),

[Time] = RIGHT(CONVERT(VARCHAR,x.CounterDateTime,0),7)

FROM (-- "Native" CounterDateTime looks similar to this

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

CROSS APPLY (-- so convert it to a date and work from there

SELECT CounterDateTime = CAST(CAST(d.CounterDateTime AS CHAR(23)) AS DATETIME)) x

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

I give up. Even if I manage to display it correctly, it's not treating the field as a timestamp so ordering gets all messed up.

Figures a Microsoft logging application would make it extremely difficult to work with another microsoft product

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Rando (1/2/2014)
...Even if I manage to display it correctly...

Using what?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

ChrisM@home (1/2/2014)
Rando (1/2/2014)
...Even if I manage to display it correctly...
Using what?

I'm getting the data from Microsoft Performance Monitor. The date field is not a date field

If I sort the data after cast/convert, 12AM comes after 11AM instead of before 1AM. Neither SSMS or Excel is treating the field as a date.

Should be

12:00AM

01:00AM

02:00AM

instead of

01:00AM

02:00AM

...

11:00AM

12:00AM

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Rando (1/2/2014)
ChrisM@home (1/2/2014)
Rando (1/2/2014)
...Even if I manage to display it correctly...
Using what?
I'm getting the data from Microsoft Performance Monitor. The date field is not a date field
If I sort the data after cast/convert, 12AM comes after 11AM instead of before 1AM. Neither SSMS or Excel is treating the field as a date.
Should be
12:00AM
01:00AM
02:00AM
instead of
01:00AM
02:00AM
...
...
11:00AM
12:00AM

I know where you're getting the data from which is why I specified this above:

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

which converts the wacky MS string-thing to a datetime, allowing you to use CONVERT or whatever to get the bits you want in the format you want. But what's consuming this data? Is it the data source for a report, for instance?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

Yes it's a data source for an Excel Pivot Chart. I'm trying to show a timeline of certain server performance metrics.

This allowed me to format the date how I would like;

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

I can't post my time code because the forum won't load and keeps timing out. It might be the firewall at work detecting something... No idea

The time code I settled on allowed me to sort it and show AM/PM but it is in 24h format instead of 12h. Not a huge deal but it's all I need to be 100% satisfied with the report.

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

That will give you date but not time. The code I posted returns datetime, from which you can extract both date and time

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

and it's intuitive too - the standard datetime as string ['2013-12-30 12:09:00.123'] is 23 characters.

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