CODEWITHSUNDEEP
sql-servert-sql
Wes Doyle
Wes Doyle

Reputation: 2287

Error when subtracting columns in SQL

I have the following query I'm using to attempt to get a date difference by subtracting values between two columns. Here is part of this query:

SELECT
ISNULL(MIN(c.CreatedOn), 0) AS [StartDate], 
ISNULL(MIN(s.CreatedOn), 0) AS [EndDate],
Duration = [EndDate]-[StartDate]
FROM MyTable
INNER JOIN...

The error that I am receiving states that I have Invalid Column Names. Is it possible to write the query this way?

Upvotes: 2

Views: 546

Answers (3)

Migo
Migo

Reputation: 151

there is a datediff function

select datediff(day, min('12/01/2014'), min(getdate()))

P.S., What are you expecting when you isnull a date data type to an int?

select isnull(cast(null as date), 0)

Upvotes: 0

Raj More
Raj More

Reputation: 48024

You cannot use aliases within the same query. If you must use them, then you can use a nested query, or a WITH statement.

Nested Query

Select StartDate, EndDate,
       Duration = [EndDate]-[StartDate]
From
(
    SELECT
    ISNULL(MIN(c.CreatedOn), 0) AS [StartDate], 
    ISNULL(MIN(s.CreatedOn), 0) AS [EndDate]
    FROM MyTable
    INNER JOIN...
) InnerQuery

WITH statement

;With InnerQuery AS
(
    SELECT
    ISNULL(MIN(c.CreatedOn), 0) AS [StartDate], 
    ISNULL(MIN(s.CreatedOn), 0) AS [EndDate]
    FROM MyTable
    INNER JOIN...
) 
Select StartDate, EndDate,
       Duration = [EndDate]-[StartDate]
From InnerQuery

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Previously defined column aliases can't be used in the same query. Use the actual calculation instead.

SELECT
ISNULL(MIN(c.CreatedOn), 0) AS [StartDate], 
ISNULL(MIN(s.CreatedOn), 0) AS [EndDate],
Duration = ISNULL(MIN(s.CreatedOn), 0)-ISNULL(MIN(c.CreatedOn), 0)
FROM MyTable
INNER JOIN...

Upvotes: 1

Related Questions