CODEWITHSUNDEEP
sqlsql-serversql-server-2008
illimite
illimite

Reputation: 99

Compare date with date in SQL Server

I am facing this problem where SQL asked me to put aliases when I'm converting chars to date format, here is my query:

declare @dateTarget char(8) = '20130613'
declare @dateTarget2 char(8) = '20180608'
declare @dateNow as date = GETDATE()

IF CAST(CONVERT(DATE, 
                LEFT(@dateTarget, 4) + SUBSTRING(@dateTarget, 5, 2) + RIGHT(@dateTarget, 2))) >= CAST(@dateNow AS DATE) 
BEGIN
    PRINT 'Target cannot be greater than DateNow.'
END
ELSE IF CAST(CONVERT(DATE, LEFT(@dateTarget2, 4) + SUBSTRING(@dateTarget2, 5, 2) + RIGHT(@dateTarget2, 2))) >= CAST(@dateNow AS DATE) 
BEGIN
    PRINT 'Target2 cannot be greater than DateNow.'
END
ELSE
BEGIN
    PRINT 'ok'
END

and these are the errors I get:

Msg 1035, Level 15, State 10, Line 5
Incorrect syntax near 'CAST', expected 'AS'.

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ELSE'.

Msg 1035, Level 15, State 10, Line 9
Incorrect syntax near 'CAST', expected 'AS'.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ELSE'.

Upvotes: 1

Views: 186

Answers (1)

Hooman Bahreini
Hooman Bahreini

Reputation: 15559

If you want to convert string to date use Cast or Convert

select CAST(@dateTarget as date) 
select CONVERT(date, @dateTarget)

SQL Server can implicitly convert your string to date (though I recommend an explicit conversion)

declare @dateTarget  char(8) = '20130613'
declare @dateTarget2  char(8) = '20180608'
declare @dateNow as date = GETDATE()

IF @dateTarget >= @dateNow
BEGIN
    print 'Target cannot be greater than DateNow.'
END
ELSE IF @dateTarget2 >= @dateNow
BEGIN
    print 'Target2 cannot be greater than DateNow.'
END
ELSE
BEGIN
    print 'ok'
END

Upvotes: 3

Related Questions