SQL advanced -> Is there are any way to retrieve milliseconds part from MS-Access date ?

QUESTION:

I'm trying to retrieve milliseconds part from MS-Access date value. Access version is 2000 (Jet-SQL 4.0). Function DatePart() returns seconds as a smallest date-part:

SELECT Now() AS current_datetime, DatePart("s", Now()) AS sec_part;

Results:
current_datetimesec_part
13/12/07 09:44:5959

Expression [DatePart("ms", Now())] works in SQLserver, but in MS Access "ms" argument is illegal. So I wander if there are any workaround to get milliseconds part of the time (the fractions of the second) ?

Theory says that date-time values are stored in mdb as a decimal number. The integer part represents the day and the decimal part represents the time as a fraction of a 24-hour day. So i can separate date and time parts of this number coverting current date to decimal number using function CDbl(), and then spliting resulting string into two parts:

SELECT Now() AS current_datetime, CDbl(Now()) AS decimal_time,
MID(STR(CDbl(Now())),1, INSTR(STR(CDbl(Now())),'.')-1) AS date_part,
MID(STR(CDbl(Now())), INSTR(STR(CDbl(Now())),'.')+1) AS time_part

Results:
current_datetimedecimal_timedate_parttime_part
13/12/07 10:53:3139429.4538310185394294538310185

Next i can even calculate amount of seconds that have to be in the "time part" of the decimal date-time representation:

SELECT Now() AS current_datetime,
DatePart("h",Now()) AS hh,
DatePart("n",Now()) AS nn,
DatePart("s",Now()) AS ss,
CDbl(Now()) AS decimal_time,
MID(STR(CDbl(Now())),1,INSTR(STR(CDbl(Now())),'.')-1) AS date_part,
MID(STR(CDbl(Now())),INSTR(STR(CDbl(Now())),'.')+1) AS time_part,
((DatePart("h",Now())*60*60)+(DatePart("n",Now())*60)+(DatePart("s",Now()))) AS seconds;

Results:
current_datetimehhnn ssdecimal_timedate_part time_partseconds
13/12/07 10:56:19105619 39429.4557754633942945577546339379

Now i have number ".455775463" that represents time in milliseconds (or not ?) and i know that there are 39379 seconds inside the time part. We need to know what does the number ".455775463" means, so by substracting from it seconds, may be the remaining will be the fractions of the second ?



sqlexamples.info