PHP - inserting datetime data

MarthaJ Sayers
MarthaJ Sayers used Ask the Experts™
on
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

Open in new window


And this is how I need the date to be:

2019-11-25 12:57:29

Open in new window


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[2];
     $wrkMonth = ChgMonth($wrkItem);  //get numerical 2 digit month
     $wrkYear = trim($wrkArrayItem[3]) ;
     $wrkDay = trim($wrkArrayItem[1]);
     $wrkTime = trim($wrkArrayItem[4]);
	
      // 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);
         $wrkFixedItDate->getTimestamp();

Open in new window


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)

Open in new window


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.
Thank you..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
are you getting the date/time as a string or a [datetime]
For the example string "Incoming Data: Mon, 25 Nov 2019 12:57:29 -0500" you want to strip off the Incoming Data: and the day of the week and it does show the timezone  -0500
$mydate=  substr("Incoming Data: Mon, 25 Nov 2019 12:57:29 -0500",20)
'convert mydate to a datetime object see https://www.php.net/manual/en/datetime.createfromformat.php
'display datetme from above in your format
' '1970-01-01 12:00:00'
date_format($date,"U = Y-m-d H:i:s");
sql wants a datetime object not a unix timestamp
'sql below
SELECT dateadd(s,$mydate,'19700101 05:00:00:000')
Distinguished Expert 2017

Commented:
You could try to use date function within php to convert, you could get SQL to convert the data before it is returned to you.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Is the column defined as date time, and what you are presenting is php interpretation/rendering?

Please post directly from the SQL server via ssms select top(1)  date_column from table_name where date_column >= '2019-11-30'
If you could post the column definition from the create table task within ssms into a new query window.
Distinguished Expert 2017

Commented:
OH forgot,
Convert(datetime, date_column,120) as date_column
Success in ‘20 With a Profitable Pricing Strategy

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!

Author

Commented:
Thank you both for responding.

David - Thank you. The data is extracted from an email and may or may not have -500 or -700 etc. I decided it was an indicator of time zones etc.  and somehow could aid in reformatting the date but I didn't know how to use it and  it doesn't always exist, I did not want to mess with it. The data is a string value and lends itself well to exploding it as an array which made it easier to extract/reconstruct the date but I knew that it needed further - and correct formatting - and it didn't seem to matter how I went about it - the date spit at me or returned a totally different value that what I expected. I knew the unexpected result was related to what MS SQL expects and what server does i.e. datetime object  vs Unix datetime stamp. I just couldn't get right.
But you set me straight again!  And helped increase my knowledge.The data is passed thru parameters to a stored procedure. This stored procedure also returns the the identity id of the newly inserted record (scope identity) o I can later update the record. When I have problems with stored procedures, and do not see any structure/syntax errors, I always turn to executing the stored procedure in the console, by going to the Stored Procedures, selecting the problem sproc, right clicking and select the option "Execute Stored Procedure". Then I copy/paste displayed data that I am trying to insert, being mindful if null values are permitted or not  based on the individual columns in the table that will receive the data. This approach has never failed me in being able to pinpoint where/what the problems may be. Resolving the issue is another matter ! I never have worked much with dates so I am excited to be able to do so. I tried different types of formatting but lack the skill.
And thank you for the link - the Php manual is always my first stop but sometimes hard to interpret.

Arnold - Thank you for your suggestions and for the link. With a previously posted question, David jogged my brain about using CONVERT - I had forgotten about it . But this time, the data is being passed to a  stored procedure via a parameter to be newly inserted into the table, (since I need the scope_identity returned of the newly inserted record to be used for updating the record later) and using CONVERT is not valid. I have worked a great deal with stored procedures but hardly any concerning passing dates.

Again, thank you both.
Distinguished Expert 2017

Commented:
Depending from which field you are extracting, you can use the offset to then adjust the time to utc

Dividing by 100 and subtracting hours from the rest
I.e. You example would turn to be Mon, 25 Nov 2019 17::57:29. (-0000)
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
In this case, I would suggest an alternative.  I typically always have 2 date fields in my rows for date_created and date_modified. Both fields have a default value set in sqlserver getdate().  This way, when you insert the row, you don't have to use either date fields in your sql that inserts the row. Both will default to the current date and time.

For an update, set your sql to getdate() as in
UPDATE myTable SET field1=@data1, date_field = getdate()

Open in new window


That can be in your SP then not passing anything from php or you can use date_field=getdate() in the sql statement generated by php. Either way you don't have to worry about formatting a date with php.  

getdate() will give you the current timestamp for the server time.  

If you need to track a time zone, either in a separate table or the current table, you can have a field for the timezone offset.  Default the offset to NULL as opposed to zero.  That way your code can check for a NULL value and you can return a message, "No timezone detected, using server time...".  Otherwise, if there is an offset detected. store that as decimal with 2 places (not all timezones are 1 hour difference https://www.timeanddate.com/time/time-zones-interesting.html).  Then you can use the offset field to display the time in the current time. The reason I do it this way is I like to keep the actual datetime or at least some constant. If you ever need to query all records updated within x and y and you have one date field that is constant like the server time, this is easy. But if you only ever stored the users time, this could be troublesome and more complex.

If I was tracking users, I would store the users time zone in the user table. The suggestion above is based on not tracking users time zone sepreatly.

Author

Commented:
Thank you for your thoughts. I too have always had two fields, DateCreated and DateModfied. And that policy has been a saving grace a few times. I never thought about having my sproc update those two fields within the sproc.
That's a good policy easy to do.  Never thought storing the time zone but that is a good idea too. With this application, the date received is very important.And the date that I am passing is pulled from incoming email.
The spoc is still spitting at - narrowed it down to the two areas -  passing money (contains only a decimal point) and the date (sigh) and I fail to see why. I will see how it goes today :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial