asked on

How can I pass this date value as a string into a MSSQL Stored Procedure?

This works:

$start="01/01/2015";
$stop="05/31/2015";

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

This does not:

$day = date('w');
$week_start = date('c', strtotime('-'.$day.' days'));
$this_week = date('c', strtotime($week_start));

$week_one_data = date('c', strtotime("$this_week - 6 weeks"));
$week_one=date("m/d/Y", strtotime($week_one_data));
//$week_one returns 09/27/2015
$week_twelve_data = date('c', strtotime("$this_week + 5 weeks"));
$week_twelve=date("m/d/Y", strtotime($week_twelve_data));
echo $week_twelve;
//$week_twelve returns 12/13/2015

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $week_one);
$stpro->bindParam(':DataDisplay_End', $week_twelve);

// call the stored procedure
$stpro->execute();

It seems to me that the reason this doesn't work is because $week_one and $week_twelve are date values as opposed to strings. How do I make my date value a string so it's read properly by the stored proc?

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce Gust

ASKER

Hey, Scott!

Seems like what you've got above is what would be added to the stored proc, yes?

It seems to me that even with implementing your suggestion, I still have the challenge of somehow converting my date into a string on the PHP side.

Do you have any input on that score?

Jose Torres

Convert it to string in the php code
$week_one=date("m/d/Y", strtotime($week_one_data));

Use the createFromFormat

Scott Pletcher

I think this is it:

$week_one=date("Ymd", strtotime($week_one_data));

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Wrong! That format is ambiguous in SQL Server and can cause date translation errors!

SET LANGUAGE english
SELECT CAST('2015-11-23' AS date) --or datetime
SET LANGUAGE german
SELECT CAST('2015-11-23' AS date) --or datetime

Compare to:
SET LANGUAGE english
SELECT CAST('20151123' AS date) --or datetime
SET LANGUAGE german
SELECT CAST('20151123' AS date) --or datetime

Use 'YYYYMMDD' in SQL Server and nothing else! If you really insist, you can use:
'YYYY-MM-DDT00:00:00'
which is also unambiguous; note that the literal 'T' and all the zeros (down thru seconds) are required to use that format.

Zberteoc

Wrong! That format is ambiguous in SQL Server and can cause date translation errors!

Ran this:

SET LANGUAGE english
SELECT CAST('20151123' AS date) as Engl_Date --or datetime
SET LANGUAGE german
SELECT CAST('20151123' AS date) as Germ_Date --or datetime

and this:
SET LANGUAGE english
SELECT CAST('2015-11-23' AS date) as Engl_Date --or datetime
SET LANGUAGE german
SELECT CAST('2015-11-23' AS date) as Germ_Date --or datetime

Open in new window

Same result:

Changed language setting to us_english.
Engl_Date
----------
2015-11-23

Die Spracheneinstellung wurde auf Deutsch geändert.
Germ_Date
----------
2015-11-23

Scott Pletcher

Hmm, yeah, looks like MS changed their code to force yyyy-mm-dd to work, but only after SQL 2008. My preference is still strongly yyyymmdd, as in Europe, people looking at it could very easily misinterpret '2015-03-01' as Jan 3 and not Mar 1, as that is the normal format there.

SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce Gust

ASKER

Gentlemen!

As always, the insight and wisdom is much appreciated.

Here's what worked:

$week_one= date("m/d/Y", strtotime("-10 months"));
$week_twelve= date("m/d/Y", strtotime("-6 months"));

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $week_one, PDO::PARAM_STR);
$stpro->bindParam(':DataDisplay_End', $week_twelve, PDO::PARAM_STR);

$stpro->execute();

It seems that by defining the datatype via PDP::PARAM_STR, the need to bind the date parameter as a string was satisfied and all is now well!

Thanks!