Php and formatting a date from a string

MarthaJ Sayers
MarthaJ Sayers used Ask the Experts™
Dates are going to be the death of me yet. I am using PHP v5.6.25
with MSSql 2014.
The column in the tabel is defined as a datetime.
Below is the code I am using and the date is exactly what I need to insert into the column.

$tz_object = new DateTimeZone('EST5EDT');
$datetime = new DateTime();
$wrkDateProcessed = $datetime->format('Y\-d\-m\ h:i:s');

Open in new window

And this is my MS SQl Insert statement:
$sqltxt = "Insert into importIDOCInfo(StoreId,Supplier,IDoc,AmtRcvd,DateProcessed)
			   values( $srtStoreId,
					(CONVERT(datetime, '$wrkDateProcessed',21)))";

Open in new window

Below is the displayed sql statement upon executing:

Insert into importIDOCInfo(StoreId,Supplier,IDoc,AmtRcvd,DateProcessed)values( 1001,'UniMat','Wed, 20 Nov 2019 18:21:05 -0500',1176.47,2019-25-11 07:25:08)

Open in new window

It simply will not do it unless I remove the time and I have to have the time in that format.
I have tried afew different ways and it just spits in my face - all day long.
I have read and read and experiment but it just spits at me.
I have tried to insert with MS SQl Manger as a query and it doesn't like it either - tells me incorrect snytax near 07
I have also used 100 and 101 instead of 21 but it doesn't like it.
Below is what my program displays:

Array ([0] => Array([0] => 42000[SQLSTATE] => 42000[1] => 102[code] => 102[2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '07'.[message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '07'.) ) 

Open in new window

Can some one please explain to me what I am doing wrong - again...sigh
Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

You should have your date in the YYYYMMDD format. You have it in the YYYYDDMM format. Just change your format string and drop the CONVERT().

$wrkDateProcessed = $datetime->format('Ymd h:i:s');

You may also need to wrap your date in quotes (I know you do in MySql - not sure about MSSQL)
I decided to set a default time zone,make a date, include strtotime, combine it and it is good now.
The time is off by 1 hour - timezone needs changed. Whew !!
Most Valuable Expert 2018
Distinguished Expert 2018

OK. That seems like a long workaround to a simple problem, but each to their own. Glad you got it working.


Your solution is simplier but I needed the seperators (dashes).
This is what worked:

// create time processed
    $tz_object = new DateTimeZone('America/New_York');
   $datetime = new DateTime();
    $wrkDateProcessed = $datetime->format('m-d-Y h:i:s'); 

Open in new window

I had a another date that I needed to extract  and massage.
It originally looked like this:

Fri, 13 Sep 2019 14:59:05 -0700 (PST)

I needed to pull out the day, month, year and time (but not the Fri and -0700 (PST)
Just this 13 Sep 2019 14:59:05  but formatted with numerical month and rearranged as to order of month, day year time being last.
So I exploded which not the greatest and I am  sure there is a easier way.
A strange thing was if I used this to contruct the datetime.

$wrkFixedDate = ($wrkmonth . '-' . $wrkMess[1] . '-'  . $wrkMess[3]  . ' ' . $wrkMess[4]);

 it spit at me because of the time  - take out the time and it would insert without using convert all good but I needed the time.
I had to change to:

$wrkFixedDate = ($wrkmonth . '/' . $wrkMess[1] . '/'  . $wrkMess[3]  . ' ' . $wrkMess[4]);

On that one, I had to use in my MSSQl the following line even thought the column in the table
was defined as datetime

(CONVERT(nvarchar, '$wrkFixedDate', 101)),

which inserted as '2019-11-20 18:21:05.000' which is still wrong.
I am very sure a better exist for situation then the one I used.
And I am looking for it.
I am going to probably post it as a question soon.

You input was apperciated - thank you for responding.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial