Dates

[ShostyFan]

I have a table with a date field set to General Date, in the table there is an input mask 99/99/0000.
I have years of data collected and now I want to create a query that shows me the time: myTime: Format([orderDate],"Short Time")
The results are 00:00.

I thought all dates were stored as a serial number so you could go back and change the format to whatever you like. Please help with what i am missing.
Thanks.

[Minty]

All dates in access are stored as a decimal number.

The integer represents the day number starting from 1 representing 31/12/1899
the decimal portion represents the time as a fraction - so right now in the immediate window gives you

?cdate(now)
15/02/2019 15:46:06
?cdbl(cdate("15/02/2019 15:44:06"))
43511.655625

Do your dates have a time value ?

[RuralGuy]

Just to set the record straight here's the description of a date/time field: https://support.microsoft.com/en-us/...crosoft-access

[ShostyFan]

Sadly my dates only have integers.
My table design says the dates are stored as General Date, so I thought this would also capture the fraction part of the date then my input mask would allow only the integer side of the date to be displayed.
Is there any hope I can get the time data to come back?

[Minty]

Personally I'd remove the input mask. They are a pain to use in real life.

Do you still have any of your original data? Can you paste it into an excel sheet and look at the actual values ?

[ShostyFan]

I created a query, copied the date data and pasted it into Excel. Here is the resulting format:

1/2/2019 0:00

[RuralGuy]

I created a query, copied the date data and pasted it into Excel. Here is the resulting format:

1/2/2019 0:00

That is consistent as there is ALWAYS a time value in a DateTime field, it is simply set to 0.

[RuralGuy]

Sadly my dates only have integers.
My table design says the dates are stored as General Date, so I thought this would also capture the fraction part of the date then my input mask would allow only the integer side of the date to be displayed.
Is there any hope I can get the time data to come back?

Just for the record, DateTime values are ALWAYS stored the same. The "General Date" to which you refer is simply a formatting option when displaying the value.

[ShostyFan]

Just for the record, DateTime values are ALWAYS stored the same. The "General Date" to which you refer is simply a formatting option when displaying the value.

I am not sure i understand, if they are always stored, how come I can't access the data? What do I need to do next?

[RuralGuy]

I'm not sure what you mean when you say "how come I can't access the data?". What can't you access?

[ShostyFan]

I'm not sure what you mean when you say "how come I can't access the data?". What can't you access?

I can't access the time part of the date. The time is being displayed as 0:00

[Micron]

If you don't expressly enter a time value when storing a value in a date field, it defaults to zero on the right side of the decimal. In your case, the data IS there and you can get it - but it sounds like all values are zero hours minutes and seconds. If you enter 02/15/2019 in a date field you will store 02/15/2019 00:00:00 AM

[Minty]

I created a query, copied the date data and pasted it into Excel. Here is the resulting format:

1/2/2019 0:00

I meant copy and paste from your original data to ensure you did have the time portion of the data, not the access version.
You had already stated none of your data in Access had any time information stored.

[orange]

I agree with Minty, copy some original dates from your Excel and see if the original capture had any non zero time values.
It could be that your input mask suspended/voided any of the time values and Access used its 0 default.

[ShostyFan]

If you don't expressly enter a time value when storing a value in a date field, it defaults to zero on the right side of the decimal. In your case, the data IS there and you can get it - but it sounds like all values are zero hours minutes and seconds. If you enter 02/15/2019 in a date field you will store 02/15/2019 00:00:00 AM

I get what you are saying. I don't want it to be true but it is
Thanks.