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:
I was able to extract the following:
and then was able to turn it into this:
But I need it in 12 hr format and I know the format needs to be this (I think)
The above produces this:
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:
Any help would be appericiated.
![Capture.PNG]()
Wed, 20 Nov 2019 18:21:05 -0500
I was able to extract the following:
20 Nov 2019 18:21:05
and then was able to turn it into this:
11/20/2019 18:21:05
But I need it in 12 hr format and I know the format needs to be this (I think)
$date = new DateTime($wrkRecvDate);
The above produces this:
2019/20/11 6:06:21:05
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')
Any help would be appericiated.
Microsoft SQL ServerPHPSQL
Last Comment
David Johnson, CD
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:
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.
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
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))
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].
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.
$wrkDate = explode(' ',$wrkRecvDate);
$wrkFixed = ($wrkDate[0] . ' ' . $wrkDate[1]);
$date = new DateTime($wrkFixed);
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?
ASKER
Thank you for responding. I did forget about convert ! I changed it and stil not go.
This is my sql:
And this is the error I receive:
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)";
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.) )
ASKER
My column is a datetime..
I dropped the line pertaining to new datetime
So it is like this:
I dropped the line pertaining to new datetime
So it is like this:
$wrkDate = explode(' ',$wrkRecvDate);
$wrkFixed = ($wrkDate[0] . ' ' . $wrkDate[1]);
what does array[0] contain?
ASKER CERTIFIED SOLUTION
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.
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.
Produces this:
And produces this error:
And my sql insert statement:
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 ;
Produces this:
Date: 11/20/2019
Time: 18:21:05
Date and Time: 11/20/2019 18:21:05
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.) )
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)";
I used a different reference and tried it in ssms 21 not 22
ASKER
Okay. I will and thank you helping. Give me about 30 minutes...pup has to go potty...
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.
using convert with dates - don't know why but I do.
Thank you for solving my issue and staying with me till resolved.
ASKER
I thank you and my pup thanks you....out we go !!
Glad I could help
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