How to get difference betwen two dates in SQL Server

Question

SELECT 
    DATEDIFF(minute, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102), CONVERT(VARCHAR, t.xxxxxx, 102)) AS daydifference 
FROM 
    (SELECT MAX(autoid), xxxxxx 
     FROM Xtable 
     WHERE uid = 3) t;

I am getting error at last "t". No column name was specified for column 1 of 't'.

[Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) - you should **always** provide a length for any `varchar` variables and parameters that you use - like this: `CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 102)` — marc_s, Feb 01 '16 at 10:56
Also: **why** are you converting everything to a `varchar` datatype?? What is the **actual** datatype of `xxxxx` in your `Xtable` ?? The `DATEDIFF` works on `DATETIME` and similar datatypes - if that column already **is** a `DATETIME`, just use it! Don't unnecessarily convert dates to strings! — marc_s, Feb 01 '16 at 10:57
the `Timestamp` datatype in SQL Server has **absolutely nothing** to do with a date and time. It's just an internal, binary counter. Use `DATE` (if you don't need time), or `DATETIME` (for SQL Server 2005) and `DATETIME2(n)` for SQL Server 2008 and newer instead — marc_s, Feb 01 '16 at 11:55

score 0 · Answer 1 · answered Feb 01 '16 at 10:34

0

Try like this. add alias to column in t

SELECT  DATEDIFF(minute, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102),
                     CONVERT(VARCHAR, t.xxxxxx, 102)) AS daydifference
    FROM    ( SELECT    MAX(autoid)  as autoid ,
                        xxxxxx as [column_name]
              FROM      Xtable
              WHERE     uid = 3
            ) t;

answered Feb 01 '16 at 10:34

Shiju Shaji

1,682
17
24

i have to place reference of xxxxx as [column_name] at t.xxxxx both are same – gsr Feb 01 '16 at 10:45
1

max(autoid) doesn't have a column name. – Shiju Shaji Feb 01 '16 at 11:09

score 0 · Answer 2 · answered Feb 01 '16 at 11:13

0

The problem is with your subquery. The first column doesn't have a name. This is not allowed. Replace:

MAX(autoid)

with

MAX(autoid) AS [ColumnName]

answered Feb 01 '16 at 11:13

David Rushton

4,915
1
17
31

t-clausen.dk · Answer 3 · 2016-02-01T12:11:10.720

This would make your query valid. HOWEVER I really don't believe this is what you are looking for:

SELECT 
    DATEDIFF(minute, CURRENT_TIMESTAMP, t.xxxxxx) AS daydifference 
FROM 
    (SELECT MAX(autoid) autoid, xxxxxx 
     FROM Xtable 
     WHERE uid = 3
     GROUP BY xxxxxx
     ) t;

Note: You need group by the columns you select, when using an aggregate function. Columns need to be named in subselects.

Not 100 % sure what you are asking. But this is what I believe you need:

SELECT TOP 1
    DATEDIFF(minute, 0, CURRENT_TIMESTAMP - xxxxxx) improvedminutedifference,
    DATEDIFF(minute, xxxxxx, CURRENT_TIMESTAMP) minutedifference
FROM 
WHERE uid = 3
ORDER BY autoid DESC

How to get difference betwen two dates in SQL Server

3 Answers3