I am using PGP v5.6.25 and MS SQL 2014.
I have a stored procedure which inserts data and returns the primary key (which is also has the identity attribute) of the newly inserted record (using SCOPE_IDENTITY()). I need the records identity to be able to update the record later in the application.
The column in the table has the datatype of datetime (not datetime2(7) nor datetimeoffset(7)).
I have narrowed the problem down to the way I construct the date from the incoming data. It's the way I am formatting it etc.
I do not know what I am doing wrong - I have not worked much with passing datetime/date to sprocs.
There is no way to know the timezone. It has to match exactly the string that I have constructed from exploding the data into a array.
The time may be in 12 hour format or 24 hour format. They do not send the timezone. That being said, I know if checking for hour >12 would help determine whither it's 12 hour format or 24 hour format - would that be so?
If I didn't need the the identity - I can easy insert using a insert statement, but I need the key and my sproc hates my date that I am passing to it.
I don't balme it - poorly formatted.
Below is how the data comes in:
Incoming Data: Mon, 25 Nov 2019 12:57:29 -0500
And this is how I need the date to be:
Below is the coding executed to extract the date and time transform what I need:
// extract and massage sent date form email header
$wrkArrayItem = explode(' ',$emailSendDate);
$wrkItem = $wrkArrayItem;
$wrkMonth = ChgMonth($wrkItem); //get numerical 2 digit month
$wrkYear = trim($wrkArrayItem) ;
$wrkDay = trim($wrkArrayItem);
$wrkTime = trim($wrkArrayItem);
// construct string date data
$wrkFixedEmailSentDate = (trim($wrkYear) . '-' . trim($wrkMonth) . '-' . trim($wrkDay) . ' ' . trim($wrkTime ));
// reformat to pass to sproc
$wrkFixedItDate = date_create_from_format('Y-m-d:h:i:s', $wrkFixedEmailSentDate);
And this is how it looks after a var_dump (I was to resolve it)
var_dump(date("Y-m-d h:i:s", strtotime("$wrkFixedItDate")));
C:\wamp\www\Emails\sproctest.php:980:string '1970-01-01 12:00:00' (length=19)
Could someone be as so kind as to set me on the right path ? I am pretty sure it might be the way I am formatting .
I will soon be doing a lot of this type of data manipulation and the application is very date oriented the very near future.