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:

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

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

FROM my_table_with_timestamp;


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: