So I have MSSQL datetime field, and in PHP I want to use this as a UNIX TIMESTAMP, because I want to use it with strtotime, in order to add 14 days into it. Eg. If it is stored in the MSSQL database as: 2010-07-27 13:12:22.040 I want to add 14 days into it to become 2010-08-10 13:12:22.040
How would I do that using PHP with MSSQL datetime field? strtotime() command takes only UNIX timestamp.
If can't be done in PHP with MSSQL, how would I do it in MSSQL and then select it with the values I am selecting?
asked Jul 27 '10 at 04:46 PM in Default
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:
Hope this helps,
As far as the pure SQL Server is concerned, yes, adding numbers of days to datetime column is trivial:
There is also a different way (disliked by purists, but nevertheless available):
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:
The above shows results similar to the ones displayed below:
Because the first int stores the number of days from the zero date, the following chicanery is possible:
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: