CODEWITHSUNDEEP
sqlsql-server
sunleo
sunleo

Reputation: 10947

SQL Server Date comparison throws error

Please help on this, how independent query works but comparison is failing for below code,I am trying to compare only dates not time so tried the query like this.

SELECT CAST(getdate() AS DATE)---'2/9/2017 12:00:00 AM'
SELECT CAST('2017/01/15' AS DATE)----'1/15/2017 12:00:00 AM'
SELECT CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE); -- Error SQLSTATE 42000

Upvotes: 1

Views: 300

Answers (5)

Ajay Prajapati
Ajay Prajapati

Reputation: 63

You can try DATEDIFF() to find out difference between 2 dates.

select DATEDIFF( DAY,(CAST('2017/01/15' AS DATE)) ,(CAST(getdate() AS DATE)))

Upvotes: 1

Pawel Czapski
Pawel Czapski

Reputation: 1864

You cannot compare in SELECT, you can use below code to determinate if date is greater or not than current date, or it is today.

DECLARE @DATE NVARCHAR(20) = '2017/01/15' 

IF (CAST(getdate() AS DATE) > CAST(@DATE AS DATE))
    PRINT 'Selected date is in past'
ELSE IF (CAST(getdate() AS DATE) = CAST(@DATE AS DATE))
    PRINT 'Selected date is today'
ELSE
    PRINT 'Selected date is in future'

Upvotes: 1

mordechai
mordechai

Reputation: 849

SELECT CAST(getdate() AS DATE)---'2/9/2017 12:00:00 AM'
SELECT CAST('2017/01/15' AS DATE)----'1/15/2017 12:00:00 AM'
SELECT IIF(CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE),1,0); --good,  you must use iif (or case when ) for predicates

Upvotes: 1

noonand
noonand

Reputation: 2855

Try something like this:

DECLARE @nowDate DATE
SELECT @nowDate = CAST(getdate() AS DATE)

DECLARE @otherDate DATE
SELECT @otherDate = CAST('2017/01/15' AS DATE)

SELECT DATEDIFF(DAY, @nowDate, @otherDate)

Upvotes: 1

juergen d
juergen d

Reputation: 204746

You need to put this condition into context. For instance like this

SELECT case when CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE) 
            then 1 
            else 0 
       end

Upvotes: 2

Related Questions