Convert nchar to time and addition functionality

  • Rock

    Old Hand

    Points: 359

    I have a scenario:

    I have a nchar field which is tagged as start time whos value is goes like this: 2300, 2330,0900,0300,1400 so on

    Other nchar field which is duration field & the values goes like 0300, 0600, 0400.

    My scenario is to add the starttime field and duration field to check if the sum value should not be greater than 08:00 AM. In others words i need to exclude the rows whos value is greater than 08:00 am

    Please suggest me

     

    Regards

    Janu

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Can you post some table DDL (CREATE TABLE statement), sample data (INSERT statements) and expected results, please?  I'm a bit confused, for example, about whether 2300 + 0900 should be treated the same as 0400 + 0400.

    John

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    ok - nchar to store  datetimes? and nchar to store durations (with leading zeros)

    why not convert the fields to datetime and int - then use dateadd or datediff to fix your issue

    better to fix the underlying issue rather than make your life harder when you have to pick up a support ticket in 2 years and say to yourself "oh boy this is going to be tough"

     

    MVDBA

  • Rock

    Old Hand

    Points: 359

    The starttime data is stored in 24hrs format. (0030,0130,0800, 0845, 1430,2345,2349, 2359 etc)

    The duration field is nchar type > 0000, 0130,0430 etc (0430 is 04 hrs and 30 mins)

    Addition of both the column has to be taken place. THe result value should not be greater than 08:00 AM

    FOr Instance > Start time value is 0600 (which is 06:00 AM) and duration is 0400 which is 04 hrs) > The addition shows it is 10:00 AM. I should exclude this criteria

  • Rock

    Old Hand

    Points: 359

    Any suggestion

  • DesNorton

    SSC-Insane

    Points: 23084

    You haven't give us the table structure as requested.  Also your examples are not readily consumable sample data.

    So, maybe this guess about your data will help

    CREATE TABLE #SampleData (
    ID int IDENTITY(1,1) NOT NULL
    , StartTime nchar(4) NOT NULL
    , Duration nchar(4) NOT NULL
    );

    INSERT INTO #SampleData ( StartTime, Duration )
    VALUES ( '2300', '0300' )
    , ( '2300', '0900' )
    , ( '0400', '0400' )
    , ( '0600', '0130' )
    , ( '0600', '0230' );


    DECLARE @MaxTime time(0) = '08:00:00';

    SELECT sd.ID
    , sd.StartTime
    , sd.Duration
    , FinishTime = CAST(CAST(STUFF( sd.StartTime, 3, 0, ':' ) AS datetime)
    + CAST(STUFF( sd.Duration, 3, 0, ':' ) AS datetime) AS time(0))
    FROM #SampleData AS sd
    WHERE @MaxTime <= CAST(CAST(STUFF( sd.StartTime, 3, 0, ':' ) AS datetime)
    + CAST(STUFF( sd.Duration, 3, 0, ':' ) AS datetime) AS time(0));
  • Rock

    Old Hand

    Points: 359

    The Above sample one works fine as it is. When I include it in my SP. It throws the below error.

    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

    BOth the columns are nchar> Data_Type = -8, Precision = 4, Length =8. Column_Def = ('0200')

    Please suggest.

    • This reply was modified 7 months, 3 weeks ago by  Rock.
  • DesNorton

    SSC-Insane

    Points: 23084

    Rock wrote:

    The Above sample one works fine as it is. When I include it in my SP. It throws the below error.

    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

    BOth the columns are nchar> Data_Type = -8, Precision = 4, Length =8. Column_Def = ('0200')

    Please suggest.

    My sample was a guess at what your data looks like.  There is no way that we can help you, unless you help us.

    We cannot see your data, so you will need to find the record(s) that result in the error.  Then we can try and identify why it is causing the error.

Viewing 8 posts - 1 through 8 (of 8 total)

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