I am trying to get a DATETIME field from a DATE and a TIME field. none of the functions in MYSQL seems useful.
Is somebody aware how to do this or that if this can even be done? :)
To combine date and time column into a timestamp, you can use cast() function with concat(). select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName; In the above concept, you will use cast() when your date and time is in string format.
The CURRENT_TIMESTAMP function in the MySQL database returns the current date and time (i.e. the time for the machine running that instance of MySQL). It is given as a value in the 'YYYY-MM-DD hh:mm:ss' format.
The current_time() function is used to get the current time. The current_timestamp() function is used to get the current date and time. The curtime() function is used to get the current time. The last_day() function is used to get the last date of the given month on the date.
Always use ANSI default string literal format for date i.e. YYYY-MM-DD like below. INSERT INTO EMPLOYEE (EMPID, FULLNAME, DESIGNATION, JOINING, SAL, DEPTNAME) VALUES(8976, 'JOHN', 'JOE', 'ANALYST', '1990-12-12', 30000, 'Analytics'); It will insert your data in RDBMS i.e. MySQL, PostgreSQL, SQL Server.
It should be as easy as
UPDATE table SET datetime_field = CONCAT(date_field, " ", time_field);
Both of the other answers do not convert the date properly if use use a TIME
of "838:00:00" which is a valid time according to the mysql manual
so instead you can try converting the time field to seconds and then adding them
for example:
date_field + INTERVAL TIME_TO_SEC(time_field) SECOND
This will convert the date accordingly
addtime(date_field, time_field)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With