Convert string to human readable date/time format

Question

Post reply

Convert string to human readable date/time format

n.heyen

SSCrazy

Points: 2849
More actions
May 29, 2009 at 12:27 pm

#204638

We have an application where the date-time stamp is stored as varchar(50) instead of datetime. I need to present this in a view so it is more human readable.
I've parsed through the string and concatenated it into close to what I need but there is a difficult part that I'm not sure how to handle.
The string is in the form:
20080916131411.000000-300
Basically the first 8 characters are the date (YYYY/MM/DD) and the next 6 are HH:MM:SS followed by microseconds (?) after the period. If I use this, I can get the time and date formatted as requested:
SELECT SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 1, 4) + '/' +
SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 5, 2) + '/' +
SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 7, 2)
AS [Last Reboot Date],
SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 9, 2) + ':' +
SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 11, 2) + ':' +
SUBSTRING(dbo.tblOperatingsystem.Lastbootuptime, 13, 2)
AS [Last Reboot Time]
But the last 4 characters are the offset from UT that I need to deal with since it would be good to have the time correct for our timezone and correct for the date if the corrected time changes that. We are in the US Central timezone.
Any thoughts how I can do this for a view in SQL 2000?
Thanks in advance,
Norman

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Oooh, this sounds like fun.

What does a date look like if it is +? Is the "-" changed to a '+'?

I'd do the conversion like you are doing, then convert the time zone information to an integer and do a DateAdd(Hour, Timezone, Date).

Something really crazy like this might work:

DECLARE @dates TABLE(date VARCHAR(50))

INSERT INTO @dates (

date

)

Select

'20080916131411.000000-300'

UNION ALL

SELECT

'20080916131411.000000+300'

SELECT

-- the outer dateadd adds the datetime offset

DATEADD(hour,

CONVERT(INT, RIGHT(date, 4))/100, -- this is the time offset

-- this dateadd adds the seconds

DATEADD(second,

CONVERT(INT, SUBSTRING(date, 13, 2)),

-- this dateadd adds the minutes

DATEADD(minute,

CONVERT(INT, SUBSTRING(date, 11, 2)),

-- this dateadd adds the houts

DATEADD(hour,

CONVERT(INT, SUBSTRING(date, 9, 2)),

/*

because 20090116 is a valid date format

I just convert the first 8 characters

to a smalldatetime.

*/

CONVERT(SMALLDATETIME, LEFT(date, 8))

) -- end of hour add

) -- end of minute add

) -- end of second add

) -- end of time offset add

AS real_date,

date

FROM

@dates

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

n.heyen SSCrazy Points: 2849 More actions · Answer 2

Thank you Jack,

This looks like it should work but will have to wait until I get back into the office to test.

As for what happens if the offset is positive? I can't say for sure; I don't have any machines where the time setting is that far off. We have a handful that report -360 (instead of -300) that I've asked the admins to check for the daylight time setting is correct or not. But everything is pretty local, nothing in Europe or Asia.

The application used is called LanSweeper. It gathers PC and server information for asset tracking and reports. The report builder is pretty basic and I get called on for anything that isn't straight forward to the admin group. Not that I'm an expert, but at least can usually get an answer. Even now the iteration I came up with (without the offset) is making them happy. This last bit is to just make it right in my mind.

Thanks again and will report back when I get into the office.

Norman

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

It definitely won't work correctly for -360 offset as my code assumes that the offset is just whole hours with the first digit hours and the other 2 minutes. I don't know of a time zone with an offset that isn't a whole hour, but there probably is one.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

I took a slightly different tact...

DECLARE @DateString VARCHAR(50)

SET @DateString = '20080916131411.000000-300'

SELECT CONVERT(DATETIME,LEFT(STUFF(STUFF(STUFF(@DateString,13,0,':'),11,0,':'),9,0,' '),21))

+ DATEADD(mm,CAST(SUBSTRING(@DateString,PATINDEX('%[-+]%',@DateString),50) AS INT),0)

Can't remember if you're supposed to add or subtract the offset. In this case, adding a negative offset is the same as subtracting it from the listed date/time.

Once you have it in a DATETIME format like this, then local regional settings on a GUI take control.... no formatting on the DB side is necessary unless you don't have a GUI. Even then, just add one more CONVERT to the whole shootin' match and you're done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I like it Jeff. I always forget about STUFF(). It's a great function.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

Jack Corbett (5/29/2009)
I like it Jeff. I always forget about STUFF(). It's a great function.

Thanks Jack. I appreciate the feedback. Just remember.... STUFF right to left if you can or you have to compensate for the characters you inserted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

n.heyen SSCrazy Points: 2849 More actions · Answer 7

The offset is the number of minutes between my time (US Central) and UT so -300 would 5 hours and -360 would be 6 hours. I may not have been clear before.

I guess I don't see how to use this to get the correct date and time. It seems somewhere the right 4 characters need to be converted to a number and added to the converted datetime string minutes and take into account the possibility of cahnging the date part if the offiset causes the time to roll over.

I guess I don't understand how this is done. Sorry for being so dense but I just am missing something.

Thanks again,

Norman

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

In my original post you just need to change the outer DATEADD to be something like this:

DATEADD(Minute, Convert(Int, Right(DateString, 4), Date)

Jeff's code already takes care of it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

n.heyen SSCrazy Points: 2849 More actions · Answer 9

Thank you Jeff for your input. When I run the example in SQL 2000, I don't get what I expected. It appears the offset is being subtracted from the month, not the minute. I used 'minute' instead of 'mm' and it seems to work correctly. Maybe there is a difference in the DATEADD function between 2000 and later editions.

For future searching, this is what I think does as expected:

DECLARE @DateString VARCHAR(50)

SET @DateString = '20080916131411.000000-300'

SELECT CONVERT(DATETIME,LEFT(STUFF(STUFF(STUFF(@DateString,13,0,':'),11,0,':'),9,0,' '),21))

+ DATEADD(minute,CAST(SUBSTRING(@DateString,PATINDEX('%[-+]%',@DateString),50) AS INT),0)

Thanks again for your advice,

Norman

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 10

n.heyen (6/3/2009)
Thank you Jeff for your input. When I run the example in SQL 2000, I don't get what I expected. It appears the offset is being subtracted from the month, not the minute. I used 'minute' instead of 'mm' and it seems to work correctly. Maybe there is a difference in the DATEADD function between 2000 and later editions.
For future searching, this is what I think does as expected:
DECLARE @DateString VARCHAR(50)
SET @DateString = '20080916131411.000000-300'
SELECT CONVERT(DATETIME,LEFT(STUFF(STUFF(STUFF(@DateString,13,0,':'),11,0,':'),9,0,' '),21))
+ DATEADD(minute,CAST(SUBSTRING(@DateString,PATINDEX('%[-+]%',@DateString),50) AS INT),0)
Thanks again for your advice,
Norman

No, you did it right. I work alot with HHMMSS and I almost always forget to use MI instead of MM on things like DATEADD. I should probably get into the habit of typing out the whole word so there's no chance of such a mistake. Thanks for the catch.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)