Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Question Unanswered: PHP query to MSSQL TimeStamp Field

    Hi,

    I am trying to retrieve the records from the MS SQL Database through PHP script.

    Retrieving record between dates that is TIMESTAMP Field.

    Here are is my query:

    $sql= "SELECT EP_EPR.\"Create Date\",EP_EPR.\"EPR Number\", EP_EPR.\"EPR Owner\", EP_EPR.\"Logged by\" FROM \"EP:EPR\" EP_EPR WHERE (EP_EPR.\"Logged by\" = 'aganesan') AND (EP_EPR.\"Create Date\" >= '2/10/2009 04:19:41 PM')";

    Create Date is the TIMESTAMP field on the database. i tried with many ways to retrieve the records, but i could not. i am struck..

    Please help me how to retrieve the records from the database field "TIMESTAMP" .

    Have attached the screen shot please check...

    Regards,
    Arun
    Attached Thumbnails Attached Thumbnails PHPTimeStampQuery.JPG   TimeStampDatabase.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you certain you want to use a TIMESTAMP column for what you are trying to do?
    are you certain you understand what a time stamp column is?

    I would have thought you would be better of using DATETIME.
    I don't see the point of having mulitiple TIMESTAMP columns in a single table.

    when supplying dates to MySQL it may make more sense to use the ISO date format yyyy mm dd
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    Hi, i am really very happy to see ur reply. thank u very much.

    I wanted to use Timestamp filed. The database is MSSQL, i am connecting through AODBC via PHP script. i checked the database it is TIMESTAMP, i searched for datetime also, but there is nothing like.

    when i try to retrive the data from the filed, i am getting "2009-01-13 16:51:52.000"

    I tried many ways to form a query. for example:
    $FromDate = date('YmdHis',( mktime (12,00,00,02,04,2009) ) );
    //$ToDate = "27/10/2003 17:50:40";
    $ToDate = date('YmdHis',( mktime (12,00,00,02,11,2009) ) );

    $lastWeek=time()-(10*24*60*60); /*figures time from week ago exactly*/
    $formatted=date('Y-m-d h:i:s',$lastWeek); /*formats time for comparison*/

    $sql= "SELECT EP_EPR.\"Create Date\",EP_EPR.\"EPR Number\", EP_EPR.\"EPR Owner\", EP_EPR.\"Logged by\" FROM \"EP:EPR\" EP_EPR WHERE (EP_EPR.\"Logged by\" = 'aganesan') AND (EP_EPR.\"Create Date\" >= '$formatted')";

    i am not getting any error from above the code but no records found..

    Records,
    Arun Kumar .G

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you investigated what the datatypes TIMESTAMP and DATETIME are?
    which, if any, of these datatypes is relevant to your problem?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2009
    Posts
    5
    Hi,

    I gone through the link and i understands well,

    here is my result when i tried with convert function.

    The SQL Server connection object is ready.

    SELECT EP_EPR."Create Date",EP_EPR."EPR Number", EP_EPR."EPR Owner", EP_EPR."Logged by" FROM "EP:EPR" EP_EPR WHERE (EP_EPR."Logged by" = 'aganesan') AND (LEFT( CONVERT(varchar, EP_EPR."Create Date", 120), 10) = '2009-02-23' )

    Warning : odbc_exec(): SQL error: Driver]Expected lexical element not found: ), SQL state 370 in SQLExecDirect in
    C:\wamp\www\AMD\Test1_Temp.php line 46
    Error in SQL

    but above the statement i didnt found any lexical error..

    Any suggestion please..

    Regards,
    Arun Kumar .G

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so you dont' see a problem with your datatypes, fair enough, but the use of TIMESTAMP looks especially weird to me.

    the expected lexical element error message means you have a fault in your SQL

    Code:
    SELECT EP_EPR."Create Date",EP_EPR."EPR Number", EP_EPR."EPR Owner", EP_EPR."Logged by" FROM "EP:EPR" EP_EPR WHERE (EP_EPR."Logged by" = 'aganesan') AND (LEFT( CONVERT(varchar, EP_EPR."Create Date", 120), 10) = '2009-02-23' )
    whoa you seem to have a few issues there
    first off you have spaces in your column names.. bad idea.. I don't know how SQL server handles those. In access you would encapsualte in [] eg [EPR Number].. but it woudl make far more sense (to me) change your naming to say EPRNumber

    you refer to a table as EP:EPR in the from clause
    FROM "EP:EPR" EP_EPR
    looks plain wrong to me
    I don't understand why you are using
    Code:
    (LEFT( CONVERT(varchar, EP_EPR."Create Date", 120), 10) = '2009-02-23' )
    if its a date value then it should already have been in a date datatype.

    as this seems to be more of a SQL server problem than a PHP problem, and given that most people using PHP don't use SQL Server do you want this question moving to the SQL server thread. Its likely you will get a more knowledgeable response to this and other SQL Server problems there. Its fine to ask PHP questions here but they ought to be problems with PHP not the underlying db engine
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2009
    Posts
    5
    Hi,

    If i use this query its working fine without any problem.

    $sql= "SELECT EP_EPR.\"Create Date\",EP_EPR.\"EPR Number\", EP_EPR.\"EPR Owner\", EP_EPR.\"Logged by\" FROM \"EP:EPR\" EP_EPR WHERE (EP_EPR.\"Logged by\" = 'aganesan') ";

    space between the column name is not a problem, its working fine..
    if u see my screenshot u will find only the TIMESTAMP Fields there is no such field like DATETIME or DATE or TIME.

    please check my two screenschot.

    1. The first query will show u the result for above query and also displayed EP_EPR."Create Date" filed values..

    2. The screen 2 i have marked left bottom of my table field name in the screen shot..

    This is what i have to find between dates (querying between Create Date) .. i am struggling here my friend past 1 week i am trying to solve this but very difficulty for me.. i am very happy that i have find u in solving some other ways in this..

    Regards,
    Arun Kumar .G

  9. #9
    Join Date
    Feb 2009
    Posts
    5
    If i get more response on this query then please put into SQL thread and after finding the ways diffidently i will forward to u.

    Regard,
    Arun Kumar .G

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you checked what the result of
    LEFT( CONVERT(varchar, EP_EPR."Create Date", 120), 10) = '2009-02-23'
    is. ie are characters 120..130 of EP_EPR."Create Date" a date?

    is it a date formatted exactly as yyyy-mm-dd. incidentally as its a microsoft product you may need to use mm/dd/yyyy, although the reference in post #5 suggests it may depend on your local formatting

    if you are using MS SQL Server that the datatype you should be using for date values is DATETIME, NOT TIMESTAMP. whether TOAD allows you to use DATETIME or SMALLDATETIME is irrelevant.. they are the datatytpes of dates and times. TIMESTAMP is used to indicate the date and time that row was last changed... so that's why you would only ever use one TIMESTAMP colum,n in any one table
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •