none
Validate Time Before Insert RRS feed

  • Question

  • Hello,

    I am bulk importing CSV file into Table1 which has am Expiry field NVARCHAR(50) - the reason its nvarchar so I can import from the csv file without errors a typical input is for example: 4:30 (4 Hours and 30 minutes) but sometimes the csv file contains a value like TBD

    I have Table2 which has an Expiry field set as datetime2, and I am using the following query to append today's date to any string with hours and minutes:

    SELECT GetDate() + CAST('4:30:00' AS TIME)  -- This works fine

    SELECT GetDate() + CAST('TBD' AS TIME)  -- This blows up and I need help with

    I tried the IsDate function but still blows up when the time is set to a string like 'TBD' or 'SQL' or 'N/A'

    Any help would be greatly appreciated, here is my full query:

    DELETE FROM t
    OUTPUT	deleted.ProcessOrder, deleted.ProductName, deleted.PlannedId, deleted.Line, deleted.Name, GetDate() + CAST(deleted.TargetStart AS TIME), 
    GetDate() + CAST(deleted.TargetComplete AS TIME) INTO Table2
    FROM Table1 t
    WHERE NOT EXISTS (SELECT 1
    FROM Table2
    WHERE t.ProcessOrder = Table3.ProcessOrder) AND
    	ProcessOrder IS NOT NULL


    Wednesday, December 4, 2019 4:49 PM

Answers

  • Do this:

     
     declare @t as varchar(8)='4:30:00'
    SELECT 
    Dateadd(minute,isnull(datepart(minute,Try_Cast(@t as time)),0)
    ,Dateadd(hour,ISNULL(datepart(hour,Try_Cast(@t as time)),0), GetDate() ))
     

    • Marked as answer by sync_master Wednesday, December 4, 2019 5:37 PM
    Wednesday, December 4, 2019 5:09 PM
    Moderator
  • DECLARE @t1 nvarchar(50) = '4:30:00'
    DECLARE @t2 nvarchar(50) = 'TBD'
    SELECT CASE WHEN ISDATE(@t1) = 1 THEN GETDATE() + CAST(@t1 AS datetime) ELSE GETDATE() END
    SELECT CASE WHEN ISDATE(@t2) = 1 THEN GETDATE() + CAST(@t2 AS datetime) ELSE GETDATE() END

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by sync_master Wednesday, December 4, 2019 5:36 PM
    Wednesday, December 4, 2019 5:26 PM

All replies

  • Neither works:

    SELECT GetDate() + TRY_CAST('TBD' AS TIME) 

    SELECT GetDate() + CAST('4:30:00' AS TIME) 

    Wednesday, December 4, 2019 5:03 PM
    Moderator
  • Do this:

     
     declare @t as varchar(8)='4:30:00'
    SELECT 
    Dateadd(minute,isnull(datepart(minute,Try_Cast(@t as time)),0)
    ,Dateadd(hour,ISNULL(datepart(hour,Try_Cast(@t as time)),0), GetDate() ))
     

    • Marked as answer by sync_master Wednesday, December 4, 2019 5:37 PM
    Wednesday, December 4, 2019 5:09 PM
    Moderator
  • DECLARE @t1 nvarchar(50) = '4:30:00'
    DECLARE @t2 nvarchar(50) = 'TBD'
    SELECT CASE WHEN ISDATE(@t1) = 1 THEN GETDATE() + CAST(@t1 AS datetime) ELSE GETDATE() END
    SELECT CASE WHEN ISDATE(@t2) = 1 THEN GETDATE() + CAST(@t2 AS datetime) ELSE GETDATE() END

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by sync_master Wednesday, December 4, 2019 5:36 PM
    Wednesday, December 4, 2019 5:26 PM
  • Thank you guys, all solutions provided work
    Wednesday, December 4, 2019 5:37 PM