CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008datetime
user556674
user556674

Reputation:

DateTime Conversion Error in T-SQL

I Have two columns Arrive_Date and Interval in a table called TimeZone. I am trying to add these two columns to get a third column which will have both Date and Interval.

My Table has data like this:

Interval        Arrive_Date
830             2010-11-01 00:00:00.000
1100            2010-11-01 00:00:00.000
1230            2010-11-02 00:00:00.000
0               2011-01-04 00:00:00.000
30              2011-03-17 00:00:00.000

I want the third column as

Interval        Arrive_Date                           Arrive_DateTime
830             2010-11-01 00:00:00.000               2010-11-01 08:30:00.000
1100            2010-11-01 00:00:00.000               2010-11-01 11:00:00.000
1230            2010-11-02 00:00:00.000               2010-11-02 12:30:00.000
0               2011-01-04 00:00:00.000               2011-01-04 00:00:00.000
30              2011-03-17 00:00:00.000               2011-03-17 00:30:00.000

I am using this query:

SELECT CAST(LEFT(CONVERT(VARCHAR,Arrive_DATE,101),10) + ' ' + LEFT(Interval,2) + ':' + RIGHT(Interval,2) + ':00'  AS DATETIME)
from TimeZone

But I am getting this Error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Can Anyone help me on this?

Upvotes: 0

Views: 618

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74187

I'd use a computed column (and I'd store the "interval" just as the desired offset in seconds from start-of-day (but that's just me):

drop table #TimeZone
go
create table #TimeZone
(
  id            int      not null identity(1,1) primary key ,
  interval_hhmm int      not null ,
  Arrive_Date   datetime not null ,
  Arrive_DateTime as dateadd(   mm , interval_hhmm % 100 , -- add the minutes
                       dateadd( hh , interval_hhmm / 100 , -- add the hours
                         convert(datetime,convert(varchar,Arrive_Date,112),112) -- make sure the base date/time value doesn't have a time component
                       )
                     ) ,
)
go

insert #TimeZone ( interval_hhmm , Arrive_Date ) values(830  , '2010-11-01 23:59:59.000')
insert #TimeZone ( interval_hhmm , Arrive_Date ) values(1100 , '2010-11-01 00:00:00.000')
insert #TimeZone ( interval_hhmm , Arrive_Date ) values(1230 , '2010-11-02 00:00:00.000')
insert #TimeZone ( interval_hhmm , Arrive_Date ) values(0    , '2011-01-04 00:00:00.000')
insert #TimeZone ( interval_hhmm , Arrive_Date ) values(30   , '2011-03-17 00:00:00.000')

select * from #timezone

Upvotes: 0

CodeZombie
CodeZombie

Reputation: 5377

What about using DATEADD() instead of these ugly string operations?

Try something like:

SELECT
    Interval % 100 AS [Minutes],
    CONVERT(INT, Interval / 100) AS [Hours],
    DATEADD(HOUR, CONVERT(INT, Interval / 100), DATEADD(MINUTE, Interval % 100, Arrive_Date)) AS [AllTogether]
FROM TimeZone

Upvotes: 0

Filip De Vos
Filip De Vos

Reputation: 11908

I would use dateadd() and math operands to get the job done. It should be faster.

select dateadd(minute, 
               Interval%100, 
               dateadd(hour, 
                       CAST(Interval/100 as int), 
                       Arrive_Date)
              )
from TimeZone

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Ok, I'm not on a computer with a database engine, so I can't test this (and I agree that they are ugly string operations), but here's one approach:

SELECT  Interval, Arrive_Date,
        CAST(CONVERT(VARCHAR(8),Arrive_Date,112) + ' ' + LEFT(RIGHT('0000'+CAST(Interval AS VARCHAR(4)),4),2)+':'+RIGHT('00'+CAST(Interval AS VARCHAR(4)),2)+':00' AS DATETIME) AS Arrive_Datetime
FROM TimeZone

Upvotes: 2

Related Questions