sql-serversql-server-2008datecomparison

How to solve the date comparison issue in SQL Server?

I am using the following way to compare two dates:

if CONVERT(varchar(20), @ScheduleDate, 101) >= CONVERT(varchar(20), @CurrentDateTime, 101)

This is working fine for the current year, but when the comes in yearly like one date is 12/31/2012 and 1/1/2013 then its not working.

Please help me how can I resolve this.

Solution

  • Why are you comparing strings? You can compare dates:

    if @ScheduleDate >= @CurrentDateTime

    but if your date contains time, I usually do:

    if convert(nvarchar(8), @ScheduleDate, 112) >= convert(nvarchar(8), @CurrentDateTime, 112)

    112 datetime format is YYYYMMDD so it's good for comparing dates.