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;

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: