Php and formatting a date from a string

MarthaJ Sayers
MarthaJ Sayers used Ask the Experts™
on
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();
$datetime->setTimezone($tz_object);
$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,
			               '$supplierCode',
				        '$wrkRecvDate',
					$wrkGrdAmt,	
					(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
Comment
Watch Question

Do more with

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

Commented:
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

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

Author

Commented:
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();
    $datetime->setTimezone($tz_object);
    $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