SQL Server and date + 1 result

In case of the SQL Server. Say, if I have a table with [myDate] column that is of the type datetime, what would adding 1 to it mean 开发者_运维百科in the following context?

SELECT * 
  FROM [myTable] 
 WHERE [myDate] + 1 > @someDate

It adds one day (exactly 24 hours)

However, your query is more correct this way. The function or processing on the column usually invalidates index usage if there is one of that column

WHERE [myDate] > @someDate - 1

OR

WHERE [myDate] > DATEADD(day, -1, @someDate)

Use the DATEADD function (see MSDN docs for DATEADD for details):

SELECT * 
  FROM dbo.myTable
 WHERE DATEADD(D, 1, myDate) > @someDate

DATEADD allows you to add any of the usual date parts (day, month, year) and also time parts (hours, minutes, seconds) etc.

A DATETIME can be expressed as a decimal value. The integer part represents the date while the decimal value represents the time.

So [DATETIME] + 1 means 'same time the next day'.

DateAdd is safer, as it assumes nothing about the underlying implementation.

It will add one day. This takes advantedge of the fact that SQL Server stores dates as two integers, one representing the number of days since day "0" - (1 jan 1900), and the second one which represents the number of ticks (about 3.33 ms) since midnight (for the time) *.

To make your queries use an index... use this formula on the input filtering parameters first, or on the "other" side of the equal sign from the tables date time field, so that the query optimizer does not have to run the calculation on every datetime field in the table to determine which rows satisfy the filter predicate. This makes your search argument "SARG-able" (Search ARGument)

 Where MyDateTimeColumn > DateAdd(day,       
         datediff(day,0, @MydateParameter), 0)    -- SARG-able

rather than

 Where DateAdd(day, datediff(day,0,       
   MyDateTimeColumn ), 0) > MydateParameter -- Not SARG-able •

In your case, this means use

   Select *
   FROM [myTable]  
   WHERE [myDate]  > @someDate - 1  

rather than

   Select *
   FROM [myTable]  
   WHERE [myDate] + 1  > @someDate   

NOTE. Internally, the second integer (the time part) stores ticks. In a day there are 24 x 60 X 60 X 300 = 25,920,000 ticks (serendipitously just below the max value a 32 bit integer can hold). However, you do not need to worry about this when arithmetically modifying a datetime... When adding or subtracting values from datetimes you can treat the value as a fraction as though it was exactly equal to the fractional portion of a day, as though the complete datetime value was a floating point number consisting of an integer portion representing the date and the fractional portion representing the time). i.e.,

  Declare @Dt DateTime Set @Dt = getdate()
  Set @Dt = @Dt + 1.0/24 -- Adds one hour
  Select @Dt
  Set @Dt = @Dt - .25 -- Moves back 6 hours
  Select @Dt

Simply adding a number to a DATE type does not work.

This works:

SELECT CAST('2014-11-21 14:30' AS DATETIME) + 1

This does not:

SELECT CAST('2014-11-21 14:30' AS DATE) + 1

This can be an issue when updating database drivers that may map a date type to DATETIME (because that's all there was in SQL2005), but change to use DATE.

It should add 1 day (24 hours) to the date, adding 0.5 will had half a day (12 hours) and adding 2 will add days (48 hours), etc. But @Barry makes a good point in his comment. You could try it and find out.

If you want to add other minutes, seconds, milliseconds, etc. You should use the DATEADD function that is built-in to SQL Server.