PHP with MSSQL: strtotime() with MSSQL DATETIME column

I believe that UNIX timestamp is simply a number of seconds from the January 1st, 1970 (midnight). Therefore, if you need to feed the datetime value from SQL Server to the strtotime() command, you should probably do something like this:

declare @i int;
set @i = datediff(second, '19700101', getDate());

-- now your int variable is ready to be fed into strtotime()

Hope this helps,

Oleg

As far as the pure SQL Server is concerned, yes, adding numbers of days to datetime column is trivial:

select dateadd(day, 14, date_column);

There is also a different way (disliked by purists, but nevertheless available):

select date_column + 14 -- to add 14 days to existing datetime column

The latter is based on the fact that internally, the datetime value (8 bytes in total) is stored in the pair of 4 byte integers. First int (4 bytes) is used to store the number of days from the zero date (1900-01-01). Second int (4 bytes) is used to store the number of ticks from midnight of the same day. This part ranges from 0 to 25919999 because there are 86400 seconds per day and every tick is about 3.33 milliseconds. Thus, the accuracy of the datetime is ~ 3.33 milliseconds. To enforce this, the rightmost digit of the milliseconds part of the datetime value can have only 3 possible values, namely 0, 3 and 7. In other words, if date1 is '2010-07-27 22:50:00.997' and the cave main attempt is made to add 1 millicecond to it to make it date1 equal to '2010-07-27 22:50:00.998', the attempt will fail, date1 is still equal to date1 is '2010-07-27 22:50:00.997'. The attempt to add 2 milliseconds to it will result it to be equal to '2010-07-27 22:50:01.000' though.

Here is the small script to show some details related to the datetime storage:

-- press Ctrl+T (results to text before executing)
set nocount on;
go

declare @d datetime; -- current date and time
declare @d_internal varbinary(8); -- how it is stored
declare @days_part varbinary(4); -- left 4 bytes of storage
declare @time_part varbinary(4); -- right 4 bytes of storage
declare @days_int int; -- number of days from zero date as int
declare @time_int int; -- number of ticks from midnight today

select 
    @d = current_timestamp, -- or use getDate(), same thing
    @d_internal = cast(@d as varbinary(8)),
    @days_part = substring(@d_internal, 1, 4),
    @time_part = substring(@d_internal, 5, 4),
    @days_int = cast(@days_part as int),
    @time_int = cast(@time_part as int);

select
    @d today, @d_internal stored_as;

select 
    @days_part left_part, @time_part right_part, 
    @days_int day_count, @time_int ticks_from_midnight;

print 'press Ctrl+D if you usually set results to grid';

set nocount off;
go

The above shows results similar to the ones displayed below:

today                   stored_as
----------------------- ------------------
2010-07-28 11:35:48.893 0x00009DC100BF1C7C

left_part  right_part day_count   ticks_from_midnight
---------- ---------- ----------- -------------------
0x00009DC1 0x00BF1C7C 40385       12524668

press Ctrl+D if you usually set results to grid

Because the first int stores the number of days from the zero date, the following chicanery is possible:

select cast(0 as datetime); -- zero date, 1900-01-01
select cast(-53690 as datetime); -- smallest date, 1753-01-01
select cast(2958463 as datetime); -- biggest date, 9999-12-31

Additionally, the minus operator (also disliked by purists) is legal. For example, if @date1 is today and @date2 is 2 days ago same time then @date1 - @date2 is equal to 3d of January 1900 at midnight. How? Here is explanation: @date1 is internally converted to number of days from January 1st 1900 until today (40385), @date2 is converted to 40383, the difference is 2 which is then converted back from int to datetime resulting in 1900-01-03

Coming back to the original question, the only limit on dateadd is that it should not result in the datetime value outside of the legal limit (from 1753-01-01 to 9999-12-31).

Some words of caution about casting the string to datetime. If one tries to cast string to datetime then cast will succeed only if the string is formatted correctly (or is spelled in 'YYYYMMDD' format which is independent of server settings). Additionally, because both int and varchar are convertible to datetime, it is essential not to forget the single quotes if the subject to cast is meant to be a string. cast(2010-07-28 as datetime) without quotes will be translated to 2010 minus 7 minus 28 = 1975, which is an int. When 1975 is attempted to be cast to datetime, SQL Server will simply add 1975 to the left int (number of days from zero date) resulting in unexpected cast result of May 30th, 1905:

select 
    cast(2010-07-28 as datetime) wrong, 
    cast('2010-07-28' as datetime) correct;

wrong                   correct
----------------------- -----------------------
1905-05-30 00:00:00.000 2010-07-28 00:00:00.000

And one last point, the datetime conversions are not as robust as they are, say, in JavaScript, which will happily translate 2010-12-35 29:75 PM into January 5th 2011 6:15 AM :)

Oleg