URGENT!!!! date/time formating 1

[Alira]

Hi!

Can't find any help with formating am/pm date/time to 24 hour format. Have a great day!

 

[lbrechler]

Use the FormatDateTime(dateVar, vbShortTime) I believe (might also have to try vbLongTime, vbShortDate, or vbLongDate, depending on what you're looking for).

 

[Alira]

if I'll use shortdate I'll loose hh:mm:ss. I need everything... Have a great day!

 

[Schroeder]

Explanations for the different formatting characters are mysteriously hard to find on the MSDN site. According to O'Reilly's VB & VBA in a Nutshell, Short Time is the only pre-defined format that will return a 24hr time.
If you want more control, do something like
Format("5:08:06 PM", "hh:mm:ss&quot
It seems that as long as you don't specify "AM/PM" or "A/P" etc., a 24hr format will be returned.

 

[Alira]

I use:

myDate2 = Format(rsAllPart.Fields(3), "m/d/yyyy h:m:ss&quot

and myDate2=5/27/2002 10:43:24 PM

is there other way? Have a great day!

 

[Norris68]

Format("5/27/2002 10:43:24 PM", "m/d/yyyy h:m:ss&quot = "5/27/2002 22:43:24" on my PC ...

 

[Schroeder]

Hmmm. What does rsAllPart.Fields(3) look like going into the Format function?
When I say: Format("5/27/2002 10:43:24 PM", "m/d/yyyy h:m:ss&quot I get "5/27/2002 22:43:24" back. I see nothing that indicates the system's date/time format should have any bearing on the operation of this function.

 

[bjd4jc]

I do this in the immediate window

?format(#1:00:00 PM#, "hh:mm:ss&quot

and get this result

13:00:00

 

[Alira]

I've changed system time and it worked.
Have a great day!

 

[Schroeder]

Well I'll be...
Seems that might present a problem if you're planning on distributing the program. What if the end user's system time is set the way yours was.
BTW how was your system time set?

 

[Alira]

More problems!
It tells me "Incorrect syntax near '22'"!!!
Why???

event_time BETWEEN #5/27/2002 22:43:24# AND #5/27/2002 22:47:24# Have a great day!

 

[Alira]

Now it's "H:mm:ss"
But it was "H:mm:ss tt"

In this case distribution is not needed...
I'm safe now
But have no idea what's wrong with a query... Have a great day!

 

[Norris68]

What back end are you using?
If SQL server, use the ISO notation:
... event_time BETWEEN {ts '2002-05-27 22:43:24'} AND {ts '2002-05-27 22:47:24'}

Useful function to generate ISO format from a Date variable:

Public Enum SQLDateEnum
    SQLDateOnly
    SQLDateTime
    SQLTimeOnly
End Enum

Public Function SQLDateFormat(InDate As Date, DateFormat As SQLDateEnum) As String
Select Case DateFormat
Case SQLDateOnly:
    SQLDateFormat = "{d '" & Format$(InDate, "yyyy-mm-dd") & "'}"
Case SQLTimeOnly:
    SQLDateFormat = "{t '" & Format$(InDate, "hh:nn:ss") & "'}"
Case SQLDateTime:
    SQLDateFormat = "{ts '" & Format$(InDate, "yyyy-mm-dd hh:nn:ss") & "'}"
End Select
End Function

 

[Alira]

Thanks a lot!!!!!!!!!!!
Have a great day!

 

[Schroeder]

Still kind of strange.. my system time is h:mm:ss tt.
Don't know what's wrong with your query. That little snippet looks fine to me (an Access backend user).

 

[Norris68]

To get round the UK/US date format problems in Access I usually format the date using a 3-character month to avoid ambiguity e.g. "dd mmm yyyy hh:nn:ss"

e.g.
... event_time BETWEEN #27 May 2002 22:43:24# AND #27 May 2002 22:47:24#