CODEWITHSUNDEEP
phpsql-server-2008datetime
valoukh
valoukh

Reputation: 561

Trouble using MSSQL datetime in PHP (via SQLSRV)

I am going round in circles with this one! I'm doing the following:

  1. Retrieving a date from an MSSQL datetime field via SQL/PHP
  2. Sending the date to a new PHP page via the querystring
  3. Trying to use that date in a new SQL query

I'm hitting problems here.

If I use echo:

echo $_REQUEST['rSessionDate'];
output: 15/10/2012

Which is fine, but when I use it in a SQL query I'm not getting the results I expect, so I thought the best thing to do would be to make sure it's being recognised as a date first.

If I use date_format():

echo date_format($_REQUEST['rSessionDate'],'Y-m-d');
output: Warning: date_format() expects parameter 1 to be DateTime, string given in ...

If I use strtotime():

echo strtotime($_REQUEST['rSessionDate']);
output: (nothing)

If I use date():

echo date('Y-m-d H:i',$_REQUEST['rSessionDate']);
output: Notice: A non well formed numeric value encountered in ...

If I use date() with strtotime():

echo date('Y-m-d H:i',strtotime($_REQUEST['rSessionDate']));
output: 1970-01-01 01:00

I'm sure I'm totally missing something simple.

EDIT: I've tried a few new functions I found:

$rSessionDate = new DateTime($_REQUEST['rSessionDate']);
echo $rSessionDate->format('Y-m-d H:i:s');
output: Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct(): Failed to parse time string (15/10/2012) at position 0 (1): Unexpected character'

and:

$rSessionDate = date_create($_REQUEST['rSessionDate']);
echo date_format($rSessionDate, 'Y-m-d H:i:s');
output: Warning: date_format() expects parameter 1 to be DateTime, boolean given i

EDIT 2:

I have tried using CAST:

SELECT fCourseCode ,fCourseTitle FROM tCourses WHERE fCourseCode = '4B' AND (ISNULL(fValidStart, 0) <= CAST('2012-10-15 00:00:00' as DATETIME))

But this fails with error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"

Upvotes: 1

Views: 7316

Answers (2)

Ashok Sen
Ashok Sen

Reputation: 183

if the date was retrieved from an MSSQL table and you want to use strtotime() in PHP and also don't want to change the date format to yyyy-mm-dd then you can use

CONVERT(VARCHAR(30), DateFromMSSQL, 121) as DateFromMSSQL

Upvotes: 0

ace300
ace300

Reputation: 70

These might help to shed some light on what you're looking for.

http://www.ozzu.com/programming-forum/php-mssql-datetime-field-not-pulling-correctly-t106226.html

http://af-design.com/blog/2010/03/13/microsoft-sql-server-driver-for-php-returns-datetime-object/

strtotime() is returning an epoch timestamp in your example above.

or check CAST and CONVERT (refers to MSSQL2000 but may still help you)
http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

Upvotes: 1

Related Questions