BigInt won't convert to proper date format

So I have a query I'm trying to write where there are two columns that will have variable results. One is date and one is time. My query will look like

Select Schedule ID , Job_Name , next_run_date , next_run_time

The values will vary depending on what database I'm running against. For example, [next_run_date] might = 20181014 and [next_run_time] might read 1000 which would be 1am. But if I run it on a different server, it could have a completely different set of values, but just the same format.

I've unsuccessfully tried to convert the columns to date/time format by using

CONVERT(varchar(10),CONVERT(date,[next_run_date],110),110) AS 'Next Run' 

And just get 'Explicit conversion from data type int to date is not allowed'

What I'd like it to display is [next_run_date] might = 10-14-2018 and [next_run_time] = 01:00. Just unsure how to convert this correctly. I do not have to write privs to the database. If I read correctly, at least for the date column, I would have to convert from Bigin to Varchar to ToDate, but unclear how to fully write that.

4 answers

  • answered 2018-10-11 19:06 Hassan Voyeau

    select convert(datetime, cast(20181014 as varchar), 102)

    Note :

    CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation specific. CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.

  • answered 2018-10-11 19:09 dnoeth

    You need to convert the bigint to a varchar first, then to a date:

    CONVERT(date,CONVERT(varchar(10),[next_run_date]),110) AS 'Next Run'
    

  • answered 2018-10-11 19:10 UnhandledExcepSean

    You could also break up the number into parts and craft a date and time.

    DECLARE @Date INT=20181014
    DECLARE @Time INT=123456
    
    SELECT CONVERT(DATE,
        SUBSTRING(CONVERT(VARCHAR(20),@Date),1,4)+'/'+
        SUBSTRING(CONVERT(VARCHAR(20),@Date),5,2)+'/'+
        SUBSTRING(CONVERT(VARCHAR(20),@Date),7,2)
    ) AS [Date]
    
    SELECT CONVERT(TIME,
        SUBSTRING(CONVERT(VARCHAR(20),@Time),1,LEN(CONVERT(VARCHAR(20),@Time))-4)+':'+
        SUBSTRING(CONVERT(VARCHAR(20),@Time),LEN(CONVERT(VARCHAR(20),@Time))-3,2)+':'+
        SUBSTRING(CONVERT(VARCHAR(20),@Time),LEN(CONVERT(VARCHAR(20),@Time))-1,2)
    ) AS [Time]
    

  • answered 2018-10-11 19:20 LukStorms

    For the time field you can stuff a : in it.

    And the date part can be done with 2 casts and a convert or format.

    The date as varchar:

    CONVERT(VARCHAR(10), CAST(CAST([next_run_date] AS VARCHAR(8)) AS DATE), 110) AS [Next Run Date],
    

    The time as varchar:

    STUFF(CAST([next_run_time] AS VARCHAR(4)),3,0,':') AS [Next Run Time]
    

    Example snippet:

    DECLARE @Table TABLE  (next_run_date INT, next_run_time INT);
    
    INSERT INTO @Table (next_run_date, next_run_time) VALUES (20181015, 1342);
    
    SELECT 
    CONVERT(VARCHAR(10), CAST(CAST([next_run_date] AS VARCHAR(8)) AS DATE), 110) AS [Next Run Date],
    STUFF(CAST([next_run_time] AS VARCHAR(4)),3,0,':') AS [Next Run Time]
    FROM @Table