Javascript date to mysql date format

How to convert node.js date to SQL Server compatible datetime?

I have a Web Api using express and Tedious to store some data on Azure SQL database. With nvarchar types and int types it works well, but when I try to save DateTime value I get an error message:

Insert into Proxy (Ip, RequisitionDate) values ('1', '2016-05-18 3:32:21' ) 

RequestError: Validation failed for parameter ‘RequisitionDate’. Invalid date.] message: ‘Validation failed for parameter \’RequisitionDate\’. Invalid date.’, code: ‘EPARAM’ >

Insert into Proxy (Ip, RequisitionDate) values ('1', '2016-05-18 3:32:21') 

var query = "Insert into Proxy (Ip,RequisitionDate) values ( '"+ ip + "', '"+ date + "' )"; console.log(query); // Insert into Proxy (Ip,RequisitionDate) values ( '1', '2016-05-18 3:32:21' ) request = new Request(query, function(err) < if (err) < console.log(err);>>); request.addParameter('Ip', TYPES.NVarChar,'SQL Server Express 2014'); request.addParameter('RequisitionDate', TYPES.DateTime , 'SQLEXPRESS2014'); connection.execSql(request); > 

3 Answers 3

It seems that you haven’t set the correct datetime value in addParameter function. According the API reference, the function is used as request.addParameter(name, type, value, [options]) .

Please try the following code:

var query = "Insert into Proxy (Ip,RequisitionDate) values ( @ip , @date)"; request = new Request(query, function(err) < if (err) < console.log(err);>>); request.addParameter('ip', TYPES.NVarChar,''); request.addParameter('date', TYPES.DateTime , new Date()); // or the set the special date, refer to https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date // request.addParameter('RequisitionDate', TYPES.DateTime , new Date(2016,05,19,3,32,21)) connection.execSql(request); > 

Any further concern, please feel free to let me know.

A lot of people are having this issue. This is how I normally solve it. Firstly, we need to understand that SQL tries to convert whatever date you send from your NodeJS code to UTC. So, how does SQL know how much difference should it add to make your date to UTC?

SHOW VARIABLES LIKE '%time_zone%'; 

if you write these command in sql query, it will show you system_time_zone and time_zone of your sql configuration. In cases, where you simply send SQL datetime without mentioning the timezone from NodeJS, it will use this time_zone to convert your date to UTC. Now, what I recommend developers is to let SQL use UTC to store date and time and only convert the date to users timezone in frontend. What I personally do is, I send datetime to SQL along with the timezone.

moment.utc().format('YYYY-MM-DD HH:mm:ss Z') 

Here Z is the timezone. The date given by this code is

Since, I used moment.utc(), the timezone is automatically set as +0:00. If I send this format to SQL to store date, it will no longer try to convert the date to UTC format. But if I do

moment.utc().format('YYYY-MM-DD HH:mm:ss'); 

SQL will convert this datetime using the system timezone offset, even if its already in UTC. I recommend you not to use dialectOptions in your database connection module, since in future it will not be supported. This is from my opinion a good way to store the date and time in sql. Now, as for showing the stored date to users. You can easily convert the UTC date stored in database to local syntax by using

moment().local().format('YYYY-MM-DD HH:mm:ss'); 

Good Luck, Have Fun Coding.

Источник

How to convert JavaScript datetime to MySQL datetime?

Date time manipulation in JavaScript is important while dealing with databases. JavaScript Date and time are different from the MySQL date and time. JavaScript provides multiple ways to represent Date and time none of these formats are the same as MySQL’s date and time format. In this article, we will discuss some approaches to converting JS date and time into MySQL date and time format.

First of all, we will understand the difference between Javascript and MySQL date and time formats. Here is an example −

ISO 8601 Date Format : YYYY-MM-DDTHH:mm:ss.sssZ

ISO 8601 Date Format: YYYY-MM-DD HH:MM:SS

Here are a few approaches to converting JS Date to MySQL date format −

Using String split() and slice() Methods

Here are the steps followed in this approach −

• Get the Javascript Date and convert that into ISO Date format using the .toISOString() method.
• Split the ISO String into two parts using the String.split( ) method with the separator using “T”
• Declare two variables data and time and assign the respective parts of the String.
• Combine the date and time strings.

Example

In this example, we convert JavaScript datetime to MySQL datetime using split() and slice() methods.

  
Convert JavaScript datetime to MySQL datetime
 
Click the following button to convert JavaScript datetime to MySQL datetime
 
 Click Here 
JavaScript Time:
 
MySQL Time:
    

After some minification the javascript code can be written as −

Using String replace() and slice() Methods

Here are the steps followed in this approach −

• Get the Javascript Date and convert that into ISO Date format using the .toISOString() method.
• Replace the T with a blank Space.
• Slice the ISO date string till the 19th character

Example

In this example, we are converting JavaScript datetime to MySQL datetime using replace() and slice() methods.

  
Convert JavaScript datetime to MySQL datetime
 
Click the following button to convert JavaScript datetime to MySQL datetime
 
 Click Here 
JavaScript Time:
 
MySQL Time:
    

After some minification the javascript code can be written as −

We have discussed here two approaches to convert JavaScript datetime to MySQL datetime with help of examples.

Источник

Convert jQuery date to php date or mysql date to be inserted correctly in database

The value of that datepicker will be sent via ajax to php function which insert a data in mysql table. The date column type from mysql database is Datetime . Every time when read value from datepicker input value, the date column in database is empty shows 00-00-0000 00:00:00 . I am newbie in php and maybe I made somewhere a mistake. the php piece of code mysqli_query($connect, «Insert into tab(date) values (‘».$myData.»‘)»); how to format javascript date in that right for mysql ?

6 Answers 6

Can you first verify that the datepicker is posting a correct value to the server?

Try alert the value somewhere.

If you have the correct input from the javascript, the php part of the script can be done like this:

if (isset$_GET['date']) $date=date("Y-m-d h:i:s",strtotime($date)); 

Echo out to confirm you have it right, and finally insert that $date into the table.

The date format in mysql is YYYY-MM-DD so you could use strtotime:

$myDataSQL = date("Y-m-d", strtotime($myData)); mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')"); 

By the way, I would recommend using prepared statements to avoid sql injection problems although it does not really matter much in this specific case. I always use prepared statements so I don’t have to think about it.

Edit: It seems strtotime needs / separators for that to work.

If you are on PHP 5.3+ you can use the DateTime class:

$date = DateTime::createFromFormat('m-d-y', $myData); $myDataSQL = $date->format('Y-m-d'); mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')"); 

As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, ‘2012-12-31’, ‘2012/12/31’, ‘2012^12^31’, and ‘2012@12@31’ are equivalent.

so you could just change the format in which datepicker is taking the date.

putting that into your update should do the trick and you wont even need additional server operations for parsing. Just make sure it will reach the server safely.

A different take on jeroen’s answer which is just as suitable

$myDataSQL = date("c", strtotime($myData)); mysqli_query($connect, "Insert into tab(date) values ('".$myDataSQL."')"); 

I find using the ISO 8601 date everywhere easier to handle timezones as they are visible. Plus it inserts into mysql.

Also in the past I have had issues with jQuery datapicker in the needing to specify a better format in its initialisation.

class DatePickerHelper < private static $map = [ "yy" =>"", "y" => "", "MM" => "", "M" => "", "mm" => "", "m" => "", "DD" => "", "D" => "", "oo" => "", "o" => "", "dd" => "", "d" => "", ]; private static $map2 = [ "" => 'Y', "" => 'y', "" => 'F', "" => 'M', "" => 'm', "" => 'n', "" => 'l', "" => 'D', "" => 'z', // note: php doesn't have "day of the year with three digits", but this is the closest "" => 'z', "" => 'd', "" => 'j', ]; private static $mapRegex = [ 'Y' => '', 'y' => '', 'm' => '', 'n' => '', 'd' => '', 'j' => '', ]; private static $mapRegex2 = [ '' => '(?P5)', '' => '(?P3)', '' => '(?P1)', '' => '(?P9)', '' => '(?P3)', '' => '(?P4)', ]; public static function convertFromDatePickerToPhpDate(string $datePickerFormat): string < $map = self::$map; $map2 = self::$map2; $first = str_replace(array_keys($map), array_values($map), $datePickerFormat); return str_replace(array_keys($map2), array_values($map2), $first); >public static function convertFromPhpDateToDatePicker(string $phpDate): string < $map2 = array_flip(self::$map2); $map = array_flip(self::$map); $first = str_replace(array_keys($map2), array_values($map2), $phpDate); return str_replace(array_keys($map), array_values($map), $first); >/** * @param string $input , the string to convert, the format of this string should match the given phpFormat * Plus, it must contain exactly: * - one day component * - one month component * - one year component * * @param string $phpFormat , all components of the phpFormat have to be one of those: * - Y: year, four digits * - y: year, two digits * - m: numeric month, with leading zeros * - n: numeric month, without leading zeros * - d: numeric day of the month, with leading zeros * - j: numeric day of the month, without leading zeros */ public static function convertFromNumericInputToMysqlDate(string $input, string $phpFormat) < $map = self::$mapRegex; $map2 = self::$mapRegex2; $first = str_replace(array_keys($map), array_values($map), $phpFormat); $pattern = str_replace(array_keys($map2), array_values($map2), $first); if (preg_match('!' . $pattern . '!', $input, $match)) < $day = $match['day_leading'] ?? $match['day_no_leading'] ?? null; if (null !== $day) < $day = (int)$day; $month = $match['month_leading'] ?? $match['month_no_leading'] ?? null; if (null !== $month) < if ( array_key_exists("year4", $match) || array_key_exists("year2", $match) ) < // a component of each type is there, we will be able to return a result if (array_key_exists("year4", $match)) < $year = (int)$match['year4']; >else < // assumed it's 20, but we don't know really, that sucks. // That's why you should use year4 instead. $year = "20" . $match['year2']; $year = (int)$year; >return $year . "-" . sprintf('%02s', $month) . "-" . sprintf("%02s", $day); > > > > return false; > > 

As in JavaScript date values are created using let date = new Date(«2017-01-26»); , you have to use the same format when formatting the date in your PHP script.

Just convert yout php date as following format date(‘Y-m-d’, $date) .

This will give you the right format for javascript field.

Источник

Convert Javascript time to MySQL format using PHP

How can I convert a js Date (like this Sun Jul 13 2014 07:00:00 GMT+0200 (EET) ) to MySQL format (like this 2014-07-13 07:00:00 ) using php ?

4 Answers 4

Since your date string already contains the time zone, you don’t need to do anything special:

$when = new DateTime('Sun Jul 13 2014 07:00:00 GMT+0200 (EET)'); echo $when->format('Y-m-d H:i:s'); 

As helpfully noted in comments, this string actually contains two bits of time zone information, UTC + 2 and EET (Eastern European Time), and PHP is basically ignoring the second one. It’s spotted better in this example:

var_dump(new DateTime('Sun Jul 13 2014 07:00:00 GMT+0200 (EET)'), DateTime::getLastErrors()); var_dump(new DateTime('Sun Jul 13 2014 07:00:00 GMT+0200'), DateTime::getLastErrors()); var_dump(new DateTime('Sun Jul 13 2014 07:00:00 (EET)'), DateTime::getLastErrors()); 

object(DateTime)#1 (3) < ["date"]=>string(26) "2014-07-13 07:00:00.000000" ["timezone_type"]=> int(1) ["timezone"]=> string(6) "+02:00" > array(4) < ["warning_count"]=>int(1) ["warnings"]=> array(1) < [34]=>string(29) "Double timezone specification" > ["error_count"]=> int(0) ["errors"]=> array(0) < >> object(DateTime)#1 (3) < ["date"]=>string(26) "2014-07-13 07:00:00.000000" ["timezone_type"]=> int(1) ["timezone"]=> string(6) "+02:00" > array(4) < ["warning_count"]=>int(0) ["warnings"]=> array(0) < >["error_count"]=> int(0) ["errors"]=> array(0) < >> object(DateTime)#1 (3) < ["date"]=>string(26) "2014-07-13 07:00:00.000000" ["timezone_type"]=> int(2) ["timezone"]=> string(3) "EET" > array(4) < ["warning_count"]=>int(0) ["warnings"]=> array(0) < >["error_count"]=> int(0) ["errors"]=> array(0) < >> 

We in fact need to strip one of them, e.g.:

$js_date_string = 'Sun Jul 13 2014 07:00:00 GMT+0200 (EET)'; // Regular expression is shown for illustration purposes, it's probably wrong! $tmp_date_string = preg_replace('/ GMT\+\d/ui', '', $js_date_string); $when = new DateTime($tmp_date_string); var_dump($when, DateTime::getLastErrors()); echo $when->format('Y-m-d H:i:s'); 

object(DateTime)#1 (3) < ["date"]=>string(26) "2014-07-13 07:00:00.000000" ["timezone_type"]=> int(2) ["timezone"]=> string(3) "EET" > array(4) < ["warning_count"]=>int(0) ["warnings"]=> array(0) < >["error_count"]=> int(0) ["errors"]=> array(0) < >> 2014-07-13 07:00:00 

Источник