MarioNote

December 28, 2009

Microsoft Access SQL to calculate the time from midnight in minutes, seconds, etc.

Filed under: Access,Database,Sample code,Software Development — mariojump @ 10:00 pm

MS Access SQL to find the time from the midnight.

MS Access’s timestmap values consist of integer part (number of days from
the certain date), and fractional part (e.g. 6am = 6/24 =0.25 day
from midnight.).

By using this, we can run a query like:

SELECT
´╗┐my_timestamp_column – INT(my_timestamp_column) AS Time_From_Midnight,
/* note: date part will be lost */

INT((my_timestamp_column – INT(my_timestamp_column))*24*60*60) AS
Seconds_From_Midnight,

INT(INT((my_timestamp_column – INT(my_timestamp_column))*24*60*60 ) /
60) AS Minutes_From_Midnight

FROM my_table_with_timestamp;

Blog at WordPress.com.