Passing date from PHP to stored procedure in MSSQL 2000

Posted on 2008-11-13
Last Modified: 2012-05-05
Hi everybody -

I've reached the extent of my tolerance for google searches, and am in need of some help.

I am passing a datetime variable from PHP to a MSSQL (2000) stored procedure. When passing the date, I am using the datatype varchar, as datetime is apparently not an option in MSSQL. I have tried a slew of 'convert' options, but can not seem to get the varchar back into a datetime format to allow my query to work. I error out with: Syntax error converting datetime from character string. (severity 16).

I've also tried different data types:
When I look at the 'date' MSSQL receives from PHP as VARCHAR, ther result is 'J'.
When I look at the 'date' MSSQL receives from PHP as SQLINT4, ther result is '0'.
When I try to pass the 'date' as text, the stored procedure says text 'cannot be compared or sorted , execpt when using IS NULL or LIKE operator'. This won't work, beacuse I need to have a range within 2 seperate exact dates.
It seems that varchar is my best best, but I'm open to other ideas.

The main goal that I am trying to accomplish is to get a date which has been entered on a webpage (in PHP) to run in a stored procedure in (MSSQL 2000) and create a temporary table based on the date from PHP. Any ideas?

Many thanks!
Question by:asdsstaff
    LVL 31

    Expert Comment

    by:James Murrell
    have you looked at it may help
    LVL 31

    Expert Comment

    by:James Murrell
    LVL 26

    Accepted Solution

    Make sure that the length of the parameter is at least 23, so varchar(23). It seem that using varchar simply it only passes 1 character to MSSQL and that's why only J, from January, June or July. What is the format that the PHP page is passing on, like Month DD, YYYY, etc? Depending on this you need to use the convert function in your stored procedure

    convert is used to interchange a datetime type with a string type back and forth depending on the format  the string is in (in) or we want to be in (out).

    Author Comment

    YES!!! Zberteoc - THANK YOU!!! My code is working properly now!

    Author Closing Comment

    I needed that little boost to figure it out! Thank you!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now