CODEWITHSUNDEEP
sql-serverzend-frameworkdate-format
softwareplay
softwareplay

Reputation: 1409

Date selection and date format in MSSQL

i have to make a select transaction with a microsoft SQL db. I have to select the entry in a given range of time. So i've made my little form with the datepicker of jquery. The datetime field in the database has this output:

 Jan 1 2014 12:00:00:000AM

This is the format of the jquery datepicker:

 {dateFormat: 'M dd yy'}

WHen i receive the GET call (but i've tested even with a POST and the output is the same)from the form i add the hours to the date (I'm using Zend framework 1.12):

    $startdate= $this->getRequest()->getParam('datepicker');
    $startdate= $startdate." 12:00:00:000AM";
    $enddate= $this->getRequest()->getParam('datepicker1');
    $enddate= $enddate." 12:00:00:000AM";

But it probably is not the right one as my select query:

 SELECT [Document No_]
       ,[Sell-to Customer No_]
       ,[Planned Delivery Date]
       ,[Description]
       ,[Description 2]
 FROM dbo.SyncroPlanningTable 
 WHERE CAST([Planned Delivery Date] as datetime)>='".$startdate."' 
  AND CAST([Planned Delivery Date] as datetime)<='".$enddate."' 
 ORDER BY [Planned Delivery Date] ASC

doesn't give any output. What am I doing wrong?

Upvotes: 0

Views: 290

Answers (1)

toddsonofodin
toddsonofodin

Reputation: 513

That may be the output of the SQL datetime column, but it's actually stored as

yyyy-mm-dd hh:mm:ss[.fff]

Thus the passed format needs to resemble

2014-01-01 12:00:00:000AM

Relevant MSDN article

Upvotes: 1

Related Questions