Convert AM/PM date time to string

[jkpats12]

Hello,

I was wondering if anyone knew of some code to convert a time such as 10/22/1992 3:00:02 PM to 1992-10-22 15:00:02.300003.

My issue is that a ODBC connection I am using allows me to connect to some tables & not to others, the ones I can't connect to I created a SQL pass-through query which lets me connect......however the SQL pass-through query brings the date in as 10/22/1992 3:00:02 PM.

I need to link multiple tables by this date & time so I need to convert the AM/PM to the longer version of the date/time field.

Thanks in advance for the help

 

[MarkK]

What I would try is to compare the values as dates rather than strings, since strings are, as you observe, so subject to formatting issues. You can ensure a field returned by a query is particular type by doing something like this...

SELECT CDate(fldDate) As MyDateTime, DateValue(fldDate) As MyDateNoTime
FROM MyTable 
WHERE SomeCondition

Failing that you can use the Format() function to format dates. The structure you've specified might look like

Format(MyDate, "yyyy-mm-dd hh:nn:ss")

but I'm not sure about the '.300003' Is that 3 tenths and 3 millionths of a second?

 

[stopher]

In a query you could use the CDate() function to convert your date string to a date. Then use the Format function to display the date how you like:

Format(CDate([myDateField]),"yyyy-mm-dd hh:mm:ss")

The above will show as 24hr clock. Take a look here for more options with Format.

hth
Chris

 

[stopher]

Sorry didn't see Lagbolts post. Essentially the same answer though.

 

[jkpats12]

Thanks LagBolt....I'll give that a shot & let you know; also those are the milliseconds that you asked.

 

[jkpats12]

Thanks to you as well Stopher.......looks like I'm almost there, only issue is the milliseconds, you guys have any ideas on that ?

I know the millisecond info is there somewhere as I described earlier it converts it into the AM/PM fromat when I link through the SQL pas-through query, so I need to get the milliseconds in order to ultimately link the tables.

Thanks again

 

[gemma-the-husky]

not sure if format(thedate,"long date") would include all the time elements you need

i am surprised that SQL is objecting to the format of a date - wouldn't the date actually be examined/tested as a datetime variable

 

[jkpats12]

Not sure either.....the same issue happens when using Crystal, but I am able to convert the AM/PM to what I need by using Char("MYField")in Crystal.....just can't seem to convert it in MS Access ????