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.


Capture.PNG
Microsoft SQL ServerPHPSQL

Avatar of undefined
Last Comment
David Johnson, CD

8/22/2022 - Mon
David Johnson, CD

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
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.
David Johnson, CD

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
MarthaJ Sayers

ASKER
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.
David Johnson, CD

is your column a DATE or a DATETIME?
MarthaJ Sayers

ASKER
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
MarthaJ Sayers

ASKER
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

David Johnson, CD

what does array[0] contain?
ASKER CERTIFIED SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MarthaJ Sayers

ASKER
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
David Johnson, CD

I used a different reference and tried it in ssms 21 not 22
MarthaJ Sayers

ASKER
Okay. I will  and thank you helping. Give me about 30 minutes...pup has to go potty...
MarthaJ Sayers

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
MarthaJ Sayers

ASKER
I thank you and my pup thanks you....out we go !!
David Johnson, CD

Glad I could help