PHP Date Formatting

MarthaJ Sayers
MarthaJ Sayers used Ask the Experts™
on
I am using PHP v 5.6.25 and MS SQL Server 2014. I have a column that is defined as a datetime. I am extracting data and this is what the data looks like that I am trying to format and insert into this column:

 Wed, 20 Nov 2019 18:21:05 -0500

Open in new window


I was able to extract the following:

20 Nov 2019 18:21:05

Open in new window


 and then was able to turn it into this:

 11/20/2019 18:21:05

Open in new window


But I need it in 12 hr format and I know the format needs to be this (I think)

$date = new DateTime($wrkRecvDate);

Open in new window


The above produces this:

2019/20/11 6:06:21:05

Open in new window


which is exactly what I wanted HOWEVER it will not insert into my
table - I have attached a image of the error message.
 I am using a sql insert statement and I have tried with/without enclosed with quotes and it will not work.
If I just use the date portion and drop the time, the record will insert just fine.
I have also tried this as to formatting:
$date = new DateTime($wrkRecvDate);
 echo $date->format('Y/d/m   g:h:i:s')

Open in new window

which produced this:2019/20/11 6:06:21:05 - again, exactly what I needed but will not insert into the column.
Any help would be appericiated.


Capture.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
what you are doing is string manipulation of the date. Internally a datetime is the number of seconds/milliseconds after a specific date (epoch)
Leave it as a date time and either in sql or your php function change the display of the date. when putting a datetime into sql you have to convert from your displayed string into a datetime

Author

Commented:
Thank you for responding. I had forgotten about that point with datetime.
I have tried inserting without converting it to 12-hour format and it still will not insert.
I have tried inserting it when I had this:

11/20/2019 18:21:05

Open in new window


No go, yet when I drop-off the time, it ready insert ! I have no clue and time is running out on this.
I am just stuck.
Top Expert 2016

Commented:
You have to convert the textual date into a datetime type value, here you can use the CONVERT function together with the format parameter The effective SQL statement should look like the following:
VALUES (CONVERT(datetime, '11/20/2019 18:21:05', 22))

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I explode the data and extract the date and time. The date is contained in $wrkDate[0] and the time is contained in $wrkDate[1].

   
  $wrkDate = explode(' ',$wrkRecvDate);
  $wrkFixed = ($wrkDate[0] . ' ' . $wrkDate[1]);
  $date = new DateTime($wrkFixed);

Open in new window


  And the program just hangs if I use $date.
But, if I drop off the time (and space ' ' . $wrkDate[1]),  and just insert $wrkDate[0],
instead of using $date,  it will insert it.
Top Expert 2016

Commented:
is your column a DATE or a DATETIME?

Author

Commented:
Thank you for responding. I did forget about convert ! I changed it and stil not go.
This is my sql:

$sqltxt = "Insert into importRpts(SupplierCode,ConfirmNbr,AmtRcvd, 
	           DateRcvd,Status,RecStatus,Description,IDOC,InvoiceNbr,StoreId) 
		   values('$supplierCode','$strInvoice', $strInvAmt,
		  (CONVERT(datetime, '$wrkFixed', 22)), $strStatus, '$strRecStatus',
		   '$strDescribe', '$strBlank', '$strInvoice', $srtStoreId)";

Open in new window


And this is the error I receive:
Array ([0] => Array([0] => 22007[SQLSTATE] => 22007[1] => 241[code] => 241[2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.[message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.) )

Open in new window

Author

Commented:
My column is a datetime..
I dropped the line pertaining to new datetime
So it is like this:
$wrkDate = explode(' ',$wrkRecvDate);
 $wrkFixed = ($wrkDate[0] . ' ' . $wrkDate[1]);

Open in new window

Top Expert 2016

Commented:
what does array[0] contain?
Top Expert 2016
Commented:
try SELECT (CONVERT (datetime,'11/20/2019 18:21:05', 21));
21 vs 22
https://www.w3schools.com/sql/func_sqlserver_convert.asp

Author

Commented:
I have no idea  - I don't reference a array[0].
The first column in the table is an indentity column, so that does not have to listed in the sql statement.
I have no idea what array[0] designates.

   // explode $wrkRecvDate
	echo 'wrkrecvdate:  ' . $wrkRecvDate . '<br><br>';
        $wrkDate = explode(' ',$wrkRecvDate);
	echo 'Date:  ' . $wrkDate[0] . '<br><br>';
	echo 'Time:  ' . $wrkDate[1] . '<br><br>';
	 //exit;
	$wrkFixed = $wrkDate[0] . ' ' . $wrkDate[1];
	echo 'Date and Time:   ' . $wrkFixed ;

Open in new window


Produces this:

Date: 11/20/2019

Time: 18:21:05

Date and Time: 11/20/2019 18:21:05

Open in new window


And produces this error:
Array ([0] => Array([0] => 22007[SQLSTATE] => 22007[1] => 241[code] => 241[2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.[message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string.) ) 

Open in new window


And my sql insert statement:
$sqltxt = "Insert into importRpts(SupplierCode,ConfirmNbr,AmtRcvd, 
	           DateRcvd,Status,RecStatus,Description,IDOC,InvoiceNbr,StoreId) 
		   values('$supplierCode','$strInvoice', $strInvAmt,
		  (CONVERT(datetime, '$wrkFixed', 22)), $strStatus, '$strRecStatus',
		   '$strDescribe', '$strBlank', '$strInvoice', $srtStoreId)";

Open in new window

Top Expert 2016

Commented:
I used a different reference and tried it in ssms 21 not 22

Author

Commented:
Okay. I will  and thank you helping. Give me about 30 minutes...pup has to go potty...

Author

Commented:
YOU are a genius !!! That did the trick !! I am rusty on my sql - been awhile and I always seem to forget about
using convert with dates - don't know why but I do.
Thank you for solving my issue and staying with me till resolved.

Author

Commented:
I thank you and my pup thanks you....out we go !!
Top Expert 2016

Commented:
Glad I could help

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