get record of date and time both

Question

how to get records date and time both from mysql database which select query is apply to fetch only date time from table

Madan Shekar · Answer

Mysql datetime functions CREATE TABLE test_dt ( id INT AUTO_INCREMENT PRIMARY KEY , created_at DATETIME ); INSERT INTO test_dt(created_at) VALUES ( '2015-11-05 14:29:36' ); Suppose you want to know which row created on 2015-11-05, you use the following query: SELECT * FROM test_dt WHERE DATE (created_at) = '2015-11-05' ; output: // 2015-11-05 14:29:36 MYSQL TIME FUNCTION To extract the time portion from a DATETIME value, you use the TIME function as the following statement SELECT TIME (@dt); output:- 13:12:20 Both Date and time run bellow query in ur mysql server SELECT @dt start, DATE_SUB(@dt, INTERVAL 1 SECOND ) '1 second before' , DATE_SUB(@dt, INTERVAL 1 MINUTE ) '1 minute before' , DATE_SUB(@dt, INTERVAL 1 HOUR ) '1 hour before' , DATE_SUB(@dt, INTERVAL 1 DAY ) '1 day before' , DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before' , DATE_SUB(@dt, INTERVAL 1 MONTH ) '1 month before' , DATE_SUB(@dt, INTERVAL 1 YEAR ) '1 year before' ;

Jignesh Kumar · Answer

Hi Krishna, Please try below query, select convert ( varchar (10), DateCoulnName, 103) [ Date ] , convert ( char (5), DateCoulnName, 108) [ time ] rom YourTableName