SQL Server saving time as a decimal? How do I convert it to timestamp?

[Vita]

Hey so I'm working with some tables from an SQL server and I can't change the format but I need to be able to read it and store it.
Currently it stores the time like "10.83973". That is an example pulled from the table. In excel I can convert it to 8:09:13PM but I need to know how to do it in Access and how to reverse it so I can store the current time as well.
Access says it is a short text field and I do not have access to the SQL database easily (I would have to ask my boss to get whatever info I needed and he is usually very busy)



So basically I am looking to understand and be able to convert 10.83973 to 8:09:13PM in Access/VBA (Assuming that is correctly converted which it seemed to be)

Appreciate the help and apologies for weird wording, I wasn't sure how to explain it really.

[Minty]

I suspect that the field is a DateTime2 data type on the SQL server, and although later versions of Access are supposed to support them they don't do a great job of it.
Is it just the time or does it have a date component as well?

I think I have answered that myself- in the immediate window :

?cdate("10.83973")
09/01/1900 20:09:13

So you can use

?timevalue(cdate("10.83973"))
20:09:13

[moke123]

?timevalue(cdate("10.83973"))
8:09:13 PM

edit: ah, Minty beat me.

[CJ_London]

date and time is stored as a decimal - the bit before the dp is the number of days since 30/12/1899, the bit after is the time expressed as the number of seconds since midnight divided by 86400 (the number of seconds in a day)

So I'm not sure what the 10 is in your example so leaving it out and using the cdate function returns

?cdate(0.83973)
20:09:13

or if you want it formatted as a string

?format(0.83973,"hh:nn:ss AM/PM")
08:09:13 PM


note with the 10, cdate produces a date

?cdate("10.83973")
09/01/1900 20:09:13

The format function will return just the time element as before

?format("10.83973","hh:nn:ss AM/PM")
08:09:13 PM

so you really need to understand what the 10 is all about

[Vita]

I suspect that the field is a DateTime2 data type on the SQL server, and although later versions of Access are supposed to support them they don't do a great job of it.
Is it just the time or does it have a date component as well?

I think I have answered that myself- in the immediate window :

?cdate("10.83973")
09/01/1900 20:09:13

So you can use

?timevalue(cdate("10.83973"))
20:09:13

This seems to work perfectly fine! Why did you have a question mark infront of it though?
Also how can I convert the current time into that format? As this form will be marking a step complete and I want it to save the current time.

Thanks so much!

[CJ_London]

Why did you have a question mark infront of it though?

that's what you use when you want to see a value or the result of a calculation/use of a function in the immediate window

Still think you need to know what the 10 is all about per post #4

how can I convert the current time into that format?

you use the time function
?time
19:54:54

Edit: or the now function
?now
27/10/2022 19:56:28

[Vita]

that's what you use when you want to see a value or the result of a calculation/use of a function in the immediate window

Still think you need to know what the 10 is all about per post #4

The 10 is because it uses DateTime2 as Minty said I presume because I get seemingly accurate times when I use it.

you use the time function
?time
19:54:54

Edit: or the now function
?now
27/10/2022 19:56:28

I need to convert the current time into the DateTime2 Format though so it can be re-entered into the SQL Db. How would I do that?

[CJ_London]

Unlike datetime, datetime2 stores the time first, then the date which is why I wonder about the 10 - strongly recommend you verify the time is correct by looking at the same value in the source system. I'm not saying it is wrong, but if it is it will be wrong in both access and excel. Not clear what 'Assuming that is correctly converted which it seemed to be' actually means, either because it looks like a time or you have check against the source system.

I don't know how to convert datetime to datetime2, but given datetime 2 provides a higher level of accuracy than you can get with datetime then I suspect just return as a datetime value and sql server will do the conversion.

[NoellaG]

I would never rely on SQL server to do conversions automatically, even not from one date type to another. Use the cast or convert functions.

[Vita]

Unlike datetime, datetime2 stores the time first, then the date which is why I wonder about the 10 - strongly recommend you verify the time is correct by looking at the same value in the source system. I'm not saying it is wrong, but if it is it will be wrong in both access and excel. Not clear what 'Assuming that is correctly converted which it seemed to be' actually means, either because it looks like a time or you have check against the source system.

I don't know how to convert datetime to datetime2, but given datetime 2 provides a higher level of accuracy than you can get with datetime then I suspect just return as a datetime value and sql server will do the conversion.

I realize it has been a while but I just realized the other day that I can check the times through a separate management software. Though I cannot seem to find any info on how it saves it.
Maybe knowing the time it should convert to would help us figure out this mystery?
I have attached a picture so you can see that there is a separate field for dates as well.


10.02702 = 8:12AM not sure how?

[CJ_London]

10.02702 = 8:12AM not sure how?

not sure whether that means you are getting 8:12AM or should be getting 8:12AM

I would expect it to be 00:38:55

[Vita]

It does equal 8:12AM. Here's a picture of the same record pulled up in the other program.

[Minty]

Well that scuppers the Idea that it is a datetime2 I think.
From the immediate window;

? cdate("10.02702")
09/01/1900 00:38:55

as CJ stated that time portion (fractional part of the number) equates to 00:38:55 AM

So maybe the 10. does have a significance we don't understand?

[CJ_London]

the time value for 8:12AM is

?cdbl(#08:12:00#)
0.341666666666667

bears no relation to

10.02702

Going back to the original post, not clear what

so I'm working with some tables from an SQL server

means - are you linked to sql server, receiving a download as a .csv? or something else

If you are linked then updating the ODBC driver might solve the problem - see this link as an example
https://social.msdn.microsoft.com/Fo...databasedesign

otherwise google something like 'datetime2 in Access'

Edit: if you want the science behind how datetime and datetime2 vary in how they are determined
https://towardsdatascience.com/datet...t-70e50ae2bab9

SO might be possible to work it backwards, but I don't have the time right now

[Micron]

You could get 8 from 10 if 10 was octal. But the rest??