Link to home
Start Free TrialLog in
Avatar of MarthaJ Sayers
MarthaJ Sayers

asked on

PHP Date Formatting

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.


User generated image
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

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
Avatar of MarthaJ Sayers
MarthaJ Sayers

ASKER

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.
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

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.
is your column a DATE or a DATETIME?
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

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

what does array[0] contain?
ASKER CERTIFIED SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

I used a different reference and tried it in ssms 21 not 22
Okay. I will  and thank you helping. Give me about 30 minutes...pup has to go potty...
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.
I thank you and my pup thanks you....out we go !!
Glad I could help