Get tomorrows date

Question

I am trying to get tomorrows date in a sql statement for a date comparison but it is not working.

Below is my code:

select * 
from tblcalendarentries
where convert(varchar,tblcalendarentries.[Start Time],101) 
      = convert(varchar, GETDATE() +1, 101)

Answer 1

To get tomorrows date you can use the below code that will add 1 day to the current system date:

SELECT DATEADD(day, 1, GETDATE())

GETDATE()

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

DATEADD(datepart , number , date)

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

So adding this to your code in the WHERE clause:

WHERE CONVERT(VARCHAR, tblcalendarentries.[Start Time], 101) = 
      CONVERT(VARCHAR, DATEADD(DAY, 1, GETDATE()), 101);

First off, GETDATE() will get you today's date in the following format:

2013-04-16 10:10:02.047

Then using DATEADD(), allows you to add (or subtract if required) a date or time interval from a specified date. So the interval could be: year, month, day, hour, minute etc.

Working with Timezones?

If you are working with systems that cross timezones, you may also want to consider using GETUTCDATE():

GETUTCDATE()

Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Answer 2

Try the below:

SELECT GETDATE() + 1

This adds one day to current date

Answer 3

Specify size of varchar in convert()

where convert(varchar(11),tblcalendarentries.[Start Time],101) = convert(varchar(11), GETDATE() +1, 101)

Answer 4

I would write:

where
  DATEADD(day,DATEDIFF(day,0,tblcalendarentries.[Start Time]),0) =
  DATEADD(day,DATEDIFF(day,0,GETDATE()),1)

This avoids converting dates to strings entirely, whilst also removing the time portion from both values.