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?
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The best format for passing a date or datetime into SQL Server  is 'YYYYMMDD', as that is 100% safe under any/all SQL settings.  A format such as "05/31/2015" can cause failures if the server has a date setting of dmy.  As an example:

SET DATEFORMAT mdy
SELECT CAST('20150531' AS date) AS date1a
SELECT CAST('05/31/2015' AS date) AS date1b
SET DATEFORMAT dmy
SELECT CAST('20150531' AS date) AS date2a
SELECT CAST('05/31/2015' AS date) AS date2b
0
brucegustPHP DeveloperAuthor Commented:
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?
0
Jose TorresSenior SQL Server DBACommented:
Convert it to string in the php code
$week_one=date("m/d/Y", strtotime($week_one_data));

Use the createFromFormat
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
I think this is it:

$week_one=date("Ymd", strtotime($week_one_data));
0
Ray PaseurCommented:
The ISO-8601 standard is the correct way to represent any internal value of date/time.  This article explains how to use the standard correctly, and how to avoid the confusion that arises when a non-standard representation is chosen.
http://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

Executive summary: Use YYYY-MM-DD and not anything else!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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.
0
ZberteocCommented:
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
0
Scott PletcherSenior DBACommented:
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.
0
ZberteocCommented:
I do remember too that here was difference between the format with - and without.

Here is the ultimate guide when it comes to SQL date and time data types:

http://www.karaszi.com/sqlserver/info_datetime.asp
0
brucegustPHP DeveloperAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.