May 29, 2009 at 12:27 pm
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
May 29, 2009 at 1:36 pm
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
May 29, 2009 at 2:55 pm
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
May 29, 2009 at 3:14 pm
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
May 29, 2009 at 3:19 pm
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
May 29, 2009 at 3:37 pm
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
May 29, 2009 at 5:52 pm
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
June 2, 2009 at 1:48 pm
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
June 2, 2009 at 1:56 pm
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
June 3, 2009 at 12:00 pm
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
June 3, 2009 at 3:23 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply