asked on

URGENT! MS ACCESS! Querying a Date/Time Field - Need ASAP!

I have a field saved as

7/6/2006 9:00:00 AM

but when I dump the var I get

2006-07-06 09:00:00.0

I need the time as well! Very Critical!

Initial Query
<cfquery datasource="#Request.App.DS#" name="GetEvent">
SELECT EventID,
EventTypeID,
EventName,
StartDateTime,
EndDateTime,
EventShortDesc,
EventDescription,
Location,
EventURL,
EventImage,
ContactName,
ContactPhone,
ContactEmail,
AllDayFlag
FROM Event
WHERE EventID = #URL.EventID#
</cfquery>

I know you can do this via Oracle...I need it for access...
TO_DATE(TO_CHAR(StartDateTime, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI') AS StartDateTime,
TO_DATE(TO_CHAR(EndDateTime, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI') AS EndDateTime,

danrosenthal

<CFOUTPUT>
StartDateTime:
#dateformat(GetEvent.StartDateTime,"MM/DD/YYYY")# #timeformat(GetEvent.StartDateTime,"HH:mm")#
<BR>

EndDateTime:
#dateformat(GetEvent.EndDateTime,"MM/DD/YYYY")# #timeformat(GetEvent.EndDateTime,"HH:mm")#
</CFOUTPUT>

mkishline

You should be able to do either:

<cfquery datasource="#Request.App.DS#" name="GetEvent">
SELECT EventID,
EventTypeID,
EventName,
Format(StartDateTime,"mm/dd/yyyy hh:nn") AS StartDateTime,
Format(EndDateTime,"mm/dd/yyyy hh:nn") AS EndDateTime,
EventShortDesc,
EventDescription,
Location,
EventURL,
EventImage,
ContactName,
ContactPhone,
ContactEmail,
AllDayFlag
FROM Event
WHERE EventID = #URL.EventID#
</cfquery>

And if that doesn't work, you can always do it when you return the values:
<cfoutput query="GetEvent">
Start Date: #DateFormat(StartDateTime,"mm/dd/yyyy")# #TimeFormat(StartDateTime,"hh:mm")#
End Date: #DateFormat(EndDateTime,"mm/dd/yyyy")# #TimeFormat(EndDateTime,"hh:mm")#
</cfoutput>

mkishline

Sorry dan, didn't mean to duplicate your stellar advice ;-)

ASKER CERTIFIED SOLUTION

mkishline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

firstbankak

ASKER

Please close this question. The individual who initiated it with me is no longer employed here.