Date type problem using PHP/MSSQL

Anon

I have a problem dealing with datetime fields in MSSQL 7.0 :

instruction like

$ShownDate = date("d-M-y",MyMSSQLDateField);

returns me a $ShownDate containing "01-01-1970"

I think Unix timestamps are integers. I know that MS timestamps are floats.
So, does anyone knows how to convert the MSSQL timestamp into a valid UNIX timestamp like the one PHP loves ?

Anon

The probleme is not that mssql save time stamp in float format, the problem is that the format of mssql datetime string is different of other database.
(in mysql : 1999-12-01 10:30:20.000
in mssql : Aut 31 1999 10:30:10.000
For have the same format use the SQL command CONVERT :
$strSQL = " SELECT
*,
Convert(varchar(10), Dto_apl, 103) AS APLDto_apl,
Convert(varchar(10), Dtx_apl, 103) AS APLDtx_apl,
Convert(varchar(10), Dt_apl_qlt, 103) AS APLDt_apl_qlt,
Convert(varchar(10), Dto_apl, 108) AS APLDto_apl_h,
Convert(varchar(10), Dtx_apl, 108) AS APLDtx_apl_h,
Convert(varchar(10), Dt_apl_qlt, 108) AS APLDt_apl_qlt_h

						FROM
							APL
						WHERE
							PKId = $ID";

so you have the right format.
PS : Excuse for my poor english.

Bye bye, hope it will help you

Anon

I have a problem dealing with datetime
fields in MSSQL 7.0 :

instruction like

$ShownDate = date("d-M-y",MyMSSQLDateField);

returns me a $ShownDate containing "01-01-1970"

try this:

$ShownDate = date("d-M-y", strtotime($MyMSSQLDateField));

this first transform the mssql-format in unix, then date() works

Anon

Along this same topic, how would you take a date genereated by date() and compare it to a date stored in a MSSQL date field?

amusarra

Try reading this post http://musarra.wordpress.com/2009/09/25/php-mssql-problemi-con-la-gestione-delle-date/ , perhaps might help you.

Bye,
Antonio.

Roger_Ramjet

FYI: MS SQL Timestamp fields are NOT Date/Time data types at all. They are binary numbers that you use to detect if the row has been updated since your application last read it. It is NOT synonymouse with the MySQL TIMESTAMP column type, which IS a Date/Time

Confusion occurs because you also have the CURRENT_TIMESTAMP function which does return a Date/Time.

Shrike

It should be noted that the MSSQL default date-time format is an arbitrary string which is defined when you create the database. That default can be changed for the session by using the SET DATEFORMAT instruction. Functions like DATEPART() can be very very useful in manipulating dates without the annoying 'out of range' errors.