text time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

text time

Author

Topic

mole999
Starting Member

49 Posts

Posted - 2014-11-01 : 04:20:22

This is the first of a series of steps i would like to accomplish in sql

I have a stored field that holds a displayable value like 1900-0700 (0ccassionaly 1900-0400 0400-0700)

I want to disassemble into 19 00 - 07 00 and rebuild into 19:00-07:00 (may need to error check )

ultimately I want to then do it again but use it to create a time value of 12 hours

I have other needs (i.e. creating an action when a datetime field is a bank holiday or weekend to change the action) but want to get a start

Mole

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-01 : 17:01:19

declare @t varchar(20) = '1900-0700'
declare @s int = cast(substring(@t, 1, charindex('-',@t)-1) as int)
declare @e int = cast(substring(@t, charindex('-',@t)+1, len(@t)) as int)
declare @st time = cast(dateadd(minute, @s/100*60+@s%100, 0) as time)
declare @et time = cast(dateadd(minute, @e/100*60+@s%100, 0) as time)

select @t, @s, @e, @st, @et

mole999
Starting Member

49 Posts

Posted - 2014-11-02 : 03:31:07

Thank you, though it fails in one of the fundamentals

declare @t varchar(25) = '1900-0400 0400-0700'

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '0400 0400-0700' to data type int.

and i'm not sure who best to resolve that

Mole

mole999
Starting Member

49 Posts

Posted - 2014-11-02 : 04:42:23

i've looked it over and for the meantime have used

declare @t varchar(25) = '1900-0400 0400-0700'

declare @s int=left(@t,4)
declare @e int=right(@t,4)

Mole

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-02 : 14:54:17

quote:
Originally posted by mole999

Thank you, though it fails in one of the fundamentals

declare @t varchar(25) = '1900-0400 0400-0700'

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '0400 0400-0700' to data type int.

and i'm not sure who best to resolve that

Mole

perhaps you mistyped something in my example? It works every time for me ad returns:

quote:

1900-0700 1900 700 19:00:00.0000000 07:00:00.0000000

mole999
Starting Member

49 Posts

Posted - 2014-11-03 : 00:50:36

I don't think so

declare @t varchar(20) = '1900-0400 0400-0600'
declare @s int = cast(substring(@t, 1, charindex('-',@t)-1) as int)
declare @e int = cast(substring(@t, charindex('-',@t)+1, len(@t)) as int)
declare @st time = cast(dateadd(minute, @s/100*60+@s%100, 0) as time)
declare @et time = cast(dateadd(minute, @e/100*60+@s%100, 0) as time)

select @t, @s, @e, @st, @et

copy and pasted then modified the top line to address the wider need

Mole

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources