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!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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))
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.
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.) )
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]);
try SELECT (CONVERT (datetime,'11/20/2019 18:21:05', 21));
21 vs 22
https://www.w3schools.com/sql/func_sqlserver_convert.asp
21 vs 22
https://www.w3schools.com/sql/func_sqlserver_convert.asp
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)";
Premium Content
You need an Expert Office subscription to comment.Start Free Trial