asked on

Php and formatting a date from a string

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

Chris Stanyon

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)

ASKER CERTIFIED SOLUTION

MarthaJ Sayers

membership

This solution is only available to members.

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

Start Free Trial

Chris Stanyon

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

MarthaJ Sayers

ASKER

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.