How to insert datetime in sql query


#1

Hey guys!

I found this website and someone mentioned that the format should be YYYY-MM-DD, is it possible to use YY-MM-DD instead?

Is this a good page for it?


#2

Why?
Use the the standard format which sql accepts. Don't overcomplicate and confuse things.

However the datetime value is stored, it can be converted to whatever format you want when you need it.


#3

How do you generate a date a month's from now?


#4

You can use DATE_ADD
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add


#5

Thanks!


#6

yes

which database are you using?


#7

depends on your database system (which OP did not mention)

more robust is to use standard sql date arithmetic --

somedate + INTERVAL 1 MONTH

How to insert multiple expiry date
#8

Correct, it is not mentioned in the topic, but from the OP's other topics I know they are using a mysql database.
The topic was moved from the php forum.


#9

Should I be using the now ()? I am not sure how to use the now () but found another site where they use..

$now = date_create('now')->format('Y-m-d H:i:s');

$tomorrow = date_create('+1 day')->format('Y-m-d H:i:s');

Does the above work and am I suppose to replace the y-m-d with the current date?


#10

SQL has its own native NOW() function which can be included in your query, so you don't have to set the current date/time in php.


#11

what happened when you tested it? ™


#12

Do I have to put the date in a variable as follows:

$now = date_create('now')->format('Y-m-d H:i:s');

Or can I just do this in my insert statement?

$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, subscriptionplan1, subscriptionplan2, subscriptionplan3, subscriptionplan4, totalfees, paidbydate, overdue, penalty_amount, user_token, user_activate) VALUES ('$first', '$last', '$email', '$uid', '$hashedPwd', '$user_permission', '$subscriptionplan1', '$subscriptionplan2', '$subscriptionplan3', '$subscriptionplan4', '$fees', '20180513 18:45:01','$overdue', '$penalty_amount', '$token', '$user_activate');";

Would this still work? '20180513 18:45:01'


#13

Can I use the following format?

$now = date("m/d/Y");

I tried it but it is now inserting the current date....


#14

I have got it to work with the now () but how can i use it to set a date a month's time? Can i do

Now () + Interval 1 month;

Is this the same as doing..
DATE_ADD(curdate(), INTERVAL 1 MONTH);

I find the now () easy to use but hopes it works for a month

Is anyone familiar with the now ()?


#15

I did try NOW() quite some time ago and found a better PhpMySql solution:

Try searching for:

PhpMySql CURRENT_TIMESTAMP

and

PhpMysql ON UPDATE CURRENT_TIMESTAMP


#16

How would i write this in my sql insert code? Insert into (date) values ('mysqli........')what is the difference between now () and phpm


#17

Nothing needs to be written into the "insert SQL code".

I think you should try another search.


#18

what happened when you tested it? ™


#20

No. The only format you can use with MySQL is YYYY-MM-DD HH:mm:ss, for example 2018-12-31 23:59:59

Nothing else will result in what you want.


#21

The language you express it in. PHP new DateTime() and MySQL NOW() are essentially the same in that return the current time, for the timezone set in PHP or MySQL respectively. When you have a PHP DateTime you can apply functions to that, like ->modify('+1 month') and then store it in MySQL (using ->format('Y-m-d H:i:s')). When you use MySQL NOW() on the other hand you can use MySQL date/time functions in your query, like NOW() + INTERVAL 30 DAY or DATE_ADD(NOW(), INTERVAL 30 DAY), which are indeed two different syntaxes for the same thing.

Which one you pick, PHP or MySQL depends on the situation. If you just need to record the current time NOW() is easiest, but once you get into testing software you'll find that NOW() is hard to test as the value is unpredictable, and you'd probably want to switch to using DateTime in PHP.
Also for more complex calculations you're probably going to want to use PHP as it can get really hard really quick in MySQL.