Solved

# Converting a text description of time to an ordinary Datetime value in SQL 2008

Posted on 2011-02-23
334 Views
I want to convert a varchar expression describing time into a date time value.  I will be given descriptions like this:  2010D320HR22MIN23
Which means the 320th day of Year 2010, Hour 22, Minute 23.
It may be equivalent to a string like this:
2010-10-19 22:23:00.0000000
(Assuming 10-19 is the 320th day of 2010, I'm not sure if this is right)
So what I want is a function that takes 2010D320HR22MIN23 as input
and returns 2010-11-19 22:23:00.0000000

I don't see any function that converts day of year to month and day. Is there one?  Or must one be built from scratch? Or is there another way of doing this?

Thanks,

C

0
Question by:cipriano555
• 2

LVL 15

Expert Comment

``````declare @x varchar(100)
set @x = '2010D320HR22MIN23'

declare @y datetime
select @y =
mi,
convert(int,  right(@x, len(@x) - charindex('MIN', @x)-2)),
hh,
convert(int, right(left(@x, charindex('MIN', @x)-1), len(left(@x, charindex('MIN', @x)-1)) - charindex('HR', @x)-1)),
dd,
convert(int, right(left(@x, charindex('HR', @x)-1), len(left(@x, charindex('HR', @x)-1)) - 5)),
convert(datetime, '1/1/' + left(@x, 4)))))
select @y
``````
0

LVL 15

Accepted Solution

derekkromm earned 500 total points
Obviously replace all instances of @x with the name of your variable or description column.

And actually, a small error since it starts on 1/1/YY and adds days, it'll be 1 day too far ahead from that original query. Updated:

``````declare @x varchar(100)
set @x = '2010D1HR22MIN23'

declare @y datetime
select @y =
mi,
convert(int,  right(@x, len(@x) - charindex('MIN', @x)-2)),
hh,
convert(int, right(left(@x, charindex('MIN', @x)-1), len(left(@x, charindex('MIN', @x)-1)) - charindex('HR', @x)-1)),
dd,
convert(int, right(left(@x, charindex('HR', @x)-1), len(left(@x, charindex('HR', @x)-1)) - 5)) - 1,
convert(datetime, '1/1/' + left(@x, 4)))))
select @y
``````
0

Author Closing Comment

Thank you so much for your help
0

## Featured Post

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …