CODEWITHSUNDEEP
sqlsql-serverdatesubtraction
PineNuts0
PineNuts0

Reputation: 5234

SQL Server : create new column that is subtraction of date column from today's date

UPDATED

Based on responses to my original question I adjusted my code to the following: 

SELECT 
    [OPCODE], [dtbuilt],
    [EVENT_2], 
    [AGE_NO_DAYS] = DATEDIFF(day, - dtbuilt, CAST(GETDATE() As DATE)) 
INTO #df_EVENT5_6 
FROM #df_EVENT5_5

Below is the error I'm receiving:

Operand data type datetime is invalid for minus operator

Values in dtbuilt column are as follows: 1999-11-29 00:00:00.000

I want to create a new column in SQL Server called AGE_NO_DAYS that is today's date minus a datetime column called dtbuilt.

Below is the code I tried but it errors out. 

SELECT 
    [OPCODE], [dtbuilt],
    [AGE_NO_DAYS] = CAST(GETDATE() As DATE) - [dtbulit]    
INTO
    #df_EVENT5_6 
FROM 
    #df_EVENT5_5

This is the error I get:

Invalid column name 'dtbulit'.

Upvotes: 2

Views: 422

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You have a misspelling in your code and you should be using dateadd(). Try:

SELECT [OPCODE], 
       [dtbuilt],
       [AGE_NO_DAYS] = DATEDIFF(day, dtbuilt, CAST(GETDATE() As DATE))    
INTO #df_EVENT5_6 
FROM #df_EVENT5_5

Note that you could do this with a computed column:

alter table #df_EVENTS_5 add age_num_days as (DATEDIFF(day, dtbuilt, CAST(GETDATE() as DATE));

Upvotes: 1

Related Questions