sql-serverdate-conversionbigintdatetime2

How to convert datetime2 column to bigint with the datediff in place


I have a column named 'order_confirmation_date' that is in Datetime2 format and I need it to work with bigint with the below query that uses datediff b/w the column value and getdate().

SELECT 
   datediff(day, convert(VARCHAR(10), 
   NULLIF((                                             
       CASE WHEN cast(replace(convert(varchar(10),cast(fpo.order_confirmation_date as date)),'-','') as bigint) >= cast([dbo].[fnGetFormatedDate](getdate(), 'YYYYMMDD') AS BIGINT)

            THEN fpo.order_confirmation_date                                                
            ELSE NULL

            END
                                        ), 0),112), GETDATE()) * (- 1) AS ordconf_x_days_fromtoday
    FROM bidb.fact_purchase_order fpo

Msg 50000, Level 16, State 2, Line 1409 Operand type clash: datetime2 is incompatible with bigint


Solution

  • You have dates already. Stop working so hard and let your dates be dates.

    declare @t table (order_confirmation_date datetime2);
    insert @t 
    values ('20190524'),('20190722'),(CAST(GETDATE() AS date))
    
    SELECT 
      fpo.order_confirmation_date,
      datediff(day, CAST(getdate() AS date),fpo.order_confirmation_date)
     AS ordconf_x_days_fromtoday
    FROM @t fpo
    

    Results:

    +-----------------------------+--------------------------+
    |   order_confirmation_date   | ordconf_x_days_fromtoday |
    +-----------------------------+--------------------------+
    | 2019-05-24 00:00:00.0000000 |                      -19 |
    | 2019-07-22 00:00:00.0000000 |                       40 |
    | 2019-06-12 00:00:00.0000000 |                        0 |
    +-----------------------------+--------------------------+
    

    Edit: If you want dates in the past to return a NULL:

    SELECT 
      fpo.order_confirmation_date,
      CASE 
        WHEN fpo.order_confirmation_date < GETDATE() THEN NULL
        ELSE datediff(day, CAST(getdate() AS date),fpo.order_confirmation_date)
      END AS ordconf_x_days_fromtoday
    FROM @t fpo