Solved

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

Posted on 2006-07-14
6
150 Views
Last Modified: 2013-12-24
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,






0
Comment
Question by:firstbankak
  • 3
6 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 17111125
<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>
0
 
LVL 12

Expert Comment

by:mkishline
ID: 17111162
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>
0
 
LVL 12

Expert Comment

by:mkishline
ID: 17111211
Sorry dan, didn't mean to duplicate your stellar advice ;-)
0
 
LVL 12

Accepted Solution

by:
mkishline earned 500 total points
ID: 17406789
It seems as though both dan and myself offered solutions that would solve firstbankak's problem. Either a point split here (or points to dan if my sql solution wasn't clear enough, since he was faster on the draw) seems most appropriate.

Thanks,
mkishline
0
 

Author Comment

by:firstbankak
ID: 17408001
Please close this question.  The individual who initiated it with me is no longer employed here.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now