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:
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')
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.
Microsoft SQL ServerPHPSQL
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:
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:
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:
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.) )
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.
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.) )
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.
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