convert a date

[pon]

I have a field Trans date with different formats, some are DD/MM/YYYY and
some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End
date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
Please help.
pon

 

[Klatuu]

Formatting has nothing to do with how the data are stored. It only presents
the data in the specified format.

It is possible some of your records may have date and time and others only
time. It should not be that way, but it is.

If you are trying to compare on date only when there may be time in the
field, you can format the values in the query so you get a corrent comparison.

 

[Michel Walsh]

You don't have if the date are date_time. By default, those without time
will be at midnight (00:00:00)

If the values are strings, better to have your default regional setting to
dd/mm/yyyy (In the USA, it would probably be mm/dd/yyyy ). If you cannot
change the regional setting (because it has to run on a PC which is not
yours), you can use DateSerial:

DateSerial( Mid(yourString, 7, 4), Mid(yourString, 4, 2), Mid(yourString,
1, 2) ) + TimeValue( CDate( yourString) )


to convert the data into a date_time value.



Vanderghast, Access MVP

 

[pon]

I created a expression
TRANS_DATE: Format([FGBTRNH_TRANS_DATE],"mm/dd/yyyy hh:nn:ss")
which worked.
Thank you
pon

 

[Michel Walsh]

You are aware that you have a string and that it is highly dependant on the
regional setting as to get the result right, right?

In fact, it is more complex than that, but just for illustration of the
possible problems (that you seem to not be fully aware), try the simple line
of code in the Immediate debug window:

? Format( CDate("31/12/2007"), "long date"), Format(CDate("12/31/2007"),
"long date")
Monday, December 31, 2007
Monday, December 31, 2007



See, you get the same date, but the first one was typed as dd/mm/yyyy and
the second as "mm/dd/yyyy".

The conclusion is to AVOID strings representation of date, if you don't
control very well the regional setting. Definitively "10/11/12" can be in
October (in USA), or in November (in England) ; the 10th of November 2012,
or, with ISO, the 12th of November 2010. String, as a date, is far, far,
from being 'safe'.


But sure, it is your data, not mine, after all.


Vanderghast, Access MVP

I created a expression
TRANS_DATE: Format([FGBTRNH_TRANS_DATE],"mm/dd/yyyy hh:nn:ss")
which worked.
Thank you
pon

You don't have if the date are date_time. By default, those without time
will be at midnight (00:00:00)

If the values are strings, better to have your default regional setting
to
dd/mm/yyyy (In the USA, it would probably be mm/dd/yyyy ). If you
cannot
change the regional setting (because it has to run on a PC which is not
yours), you can use DateSerial:

DateSerial( Mid(yourString, 7, 4), Mid(yourString, 4, 2),
Mid(yourString,
1, 2) ) + TimeValue( CDate( yourString) )


to convert the data into a date_time value.



Vanderghast, Access MVP