Solved Date to DateTime (1 Viewer)

[AlexN]

Hi everyone again,

I have a date field in a query and I want to convert it to a datetime one.
e.g I want 01/01/2017 to become 01/01/2017 00:00:00
but,
result should also be a date field, not a string (as a Format function outcome)


Any ideas?

 

[arnelgp]

you can't have it, since Access automatically truncate 00:00:00.
just display in Format() the datefield and in calculation use the field value.

 

[ebs17]

Use the Format property, not the Format function. But this is better for the text box in the displayed form or report. As a rule, it is not the task of a query or a table to display formatting.

 

[AlexN]

thank you both @arnelgp, @ebs17
This field's value would later be used in vba code to position records sorted by date and time (after some calculations). Since the field is a string (no matter how it appears) can't do that.
Am I misinterpreting something?

p.s. Changing the field's type in the table is out of the question, I would rather build the whole project from zero.

 

[ebs17]

DateTime is converted internally as a double, i.e. it is a number. So you can use number sorting immediately.

? #1/1/2017# * 1, #1/1/2017 00:00:00# * 1, #1/1/2017 08:30:15# * 1
 42736         42736         42736,3543402778

 

[AlexN]

DateTime is converted internally as a double, i.e. it is a number. So you can use number sorting immediately.

? #1/1/2017# * 1, #1/1/2017 00:00:00# * 1, #1/1/2017 08:30:15# * 1
42736         42736         42736,3543402778

Well, I suppose I should put the resulting string between #s and then use the DateSerial function ? Is that what you're suggesting or I'm wrong again?

 

[ebs17]

or I'm wrong again?

Yes.

records sorted by date and time

Just do it with no extra formatting. You quickly notice the difference between sorted numbers and sorted strings.

Dim myDate As Date
myDate = #1/1/2017#

The hashes are the literals that show in VBA and SQL that it is a date (including a possible time portion).

 

[AlexN]

Yes.


Just do it with no extra formatting. You quickly notice the difference between sorted numbers and sorted strings.

Dim myDate As Date
myDate = #1/1/2017#

The hashes are the literals that show in VBA and SQL that it is a date (including a possible time portion).

Thank you,
I'll give it a try but I feel it won't work.
Somewhere in my vba code I use DateValue(FieldA) and TimeValue(FieldA) (FieldA being the one in question), and I don't think FieldA being a string will give proper results.

 

[ebs17]

DateValue and TimeValue ... take a date value as an argument and return Date. There are no strings, unless you build them in specially.

 

[AlexN]

DateValue and TimeValue ... take a date value as an argument and return Date. There are no strings, unless you build them in specially.

ok let's go again from the start.
I have a date field Field1 (e.g 01/01/2017).
i want to convert it to a datetime field FieldA (e.g 01/01/2017 00:00:00).
If I use the Format function I'll get FieldA value = 01/01/2017 00:00:00 but it will be a string not a date. Therefore in my vba code DateValue(FieldA) and TimeValue(FieldA) won't give valid results.

 

[ebs17]

i want to convert it to a datetime field FieldA (e.g 01/01/2017 00:00:00)

Unnecessary. A Date field is a DateTime field.

01/01/2017 = 01/01/2017 00:00:00 => true

Who looks in VBA, who wants to see formats ?

 

[AlexN]

Unnecessary. A Date field is a DateTime field.

01/01/2017 = 01/01/2017 00:00:00 => true

But 01/01/2017 is also 01/01/2017 00:08:30 ?

 

[AlexN]

Any way it's getting too philosophical and too complicated.
Is there any way (custom function) to turn the Format([Field1],"yyyy/mm/dd hh:nn:ss") value to a date value or the answer is No as @arnelgp initially stated?
In that case I'll have to do it all over from the start

 

[ebs17]

No.
00:08:30 <> 00:00:00

It is important that you understand the date data type (everyone should). This is implemented internally as a double. The integers are the elapsed days since 1899-12-30. The decimal places are fractions of a day, so an hour is 1/24 etc. Also analyze the example calculations.

? Now * 1, Date * 1, Time * 1       
 44950,6013425926            44950         0,601342592592593

? DateDiff("d", #12/30/1899#, Date)
 44950

? 1 / 24, #01:00:00# * 1
 4,16666666666667E-02        4,16666666666667E-02

Format (string) to Date: Use CDate
However, Cdate does not accept any format, usually only those from your own regional settings.

After we've gone around in circles here several times, I feel dizzy and stop right there.

 

[AlexN]

No.
00:08:30 <> 00:00:00

It is important that you understand the date data type (everyone should). This is implemented internally as a double. The integers are the elapsed days since 1899-12-30. The decimal places are fractions of a day, so an hour is 1/24 etc. Also analyze the example calculations.

? Now * 1, Date * 1, Time * 1      
44950,6013425926            44950         0,601342592592593

? DateDiff("d", #12/30/1899#, Date)
44950

? 1 / 24, #01:00:00# * 1
4,16666666666667E-02        4,16666666666667E-02

Format (string) to Date: Use CDate
However, Cdate does not accept any format, usually only those from your own regional settings.

After we've gone around in circles here several times, I feel dizzy and stop right there.

That said (and understood) I think I'm gonna do it all over again because there's no way I can calculate day's fragments the way it is.
Thank you so much.

 

[The_Doc_Man]

Hi everyone again,

I have a date field in a query and I want to convert it to a datetime one.
e.g I want 01/01/2017 to become 01/01/2017 00:00:00
but,
result should also be a date field, not a string (as a Format function outcome)


Any ideas?

In the absence of any conversion function, fields in a query retain the data type property of their origin. You are confusing the issue because the query's field will be a string if the source-field in the query's table source is a string. It will be a date if the query's table source is a date. If you get a date without a time it is because the time got truncated away as explained earlier. You are confusing "intrinsic display format" with "actual data storage" format.

Internally, a DATE field is a TYPECAST of type DOUBLE. If you aren't sure, I'll be explicit. A typecast field is stored one way but interpreted another way, and Access knows how to interpret the two types differently even though internally they are the same. That is the same issue as Boolean or Yes/No fields, which are typecasts of BYTE (integer). Stored as a byte, they are interpreted as TRUE or FALSE.

Back to DATE fields... Just like putting the double-quote marks (") around something makes it a string, putting octothorpes (#) around something makes it a DATE (though of course if you give an impossible input string for that, it fails.)

This field's value would later be used in vba code to position records sorted by date and time (after some calculations). Since the field is a string (no matter how it appears) can't do that. Am I misinterpreting something?

Yes. Using a format function, you perform a type conversion (to string) - but that isn't what you HAVE to store or use. Using a control or field format property, the implied type conversion ONLY applies to what is displayed on screen or in print, not what is stored in the record - UNLESS you actually store that formatted date into a STRING field or variable.

Forgive me if I get a bit pedantic here, but I need to emphasize a point. Computers in general and Access in particular provide lots of "illusions" such that you can't always believe your eyes. If you display an integer, it was just a stream of bits. If you display a scientific number, it was just a stream of bits... and so on. It is all about what is stored vs. what is shown. It DOESN'T MATTER how something is shown.

To use something in a computation is different from using something for display. It comes back to how you store it (or in WHAT you store it.) It DOESN'T MATTER how the query shows the field. It matters where and how you put it away for later use. Your concern over how the query displayed the field is misplaced. You DO have to treat the field consistently with regard to how you store it and how you use it, but you can separately display the field in any way you want - as long as you don't allow the output of a FORMAT function to be used in place of the actual data element.

I don't know if I confused you more or if the point got through. But what I saw was someone having issues with form vs. function. I hope that I adequately addressed that.

 

[AlexN]

Thank you so much @The_Doc_Man,
Although I knew (or thought so) the difference between "display format" and "storage format", you've been more explanatory than everything I've read so far.
Problem still exists though. The more I dive into it the more I understand that it's a design problem. I think I should have made my original date field the date AND time type right from the start. So far, having already input a couple of tens of thousands of records , time value wasn't that crucial, but now needing to input a different kind of records it has become so. So I'm trying to find out a walk around to avoid deleting the whole project and designing it from scratch.

Thanks again.

 

[Pat Hartman]

There is NO data type in Access that just stores a date. The date/time data type stores the date plus the time of day - ALWAYS. If your data does not include the time element, it is because you have been using the Date() function or just typing in the date. There really is no way to go back and assign a time for the existing records. But for new records, use the Now() function or enter both the date and the time. Most of my tables have a field named CreateDT. I set its default value to Now() and Access automatically populates each new record with a timestamp.

 

[The_Doc_Man]

I think I should have made my original date field the date AND time type right from the start.

Access calls it a date field but there IS no "date and time" field type. It is just DATE and it explicitly INCLUDES time.

 

[AlexN]

@Pat Hartman
Thank you,
I was using the pop up calendar control to input date in that field so yes, it was only date. Problem is at that time, time wasn't needed but now is. So here we go again ...

@The_Doc_Man
Thank you,
I know date, date/time, date/time extended are the same, and I also know now that I've done wrong in my design