Ask a Question related to Coldfusion - Getting Started, Design and Development.
-
mxstu #1
Re: Date/Time Issue
The code looks okay. Are "EventTime" and "EventTimeTo" date/time columns in
your table? If you output the query values without the LSTimeFormat()
functions, what are the values shown? Something like...?
{ts '1899-12-30 10:00:00'}-{ts '1899-12-30 13:00:00'}
mxstu Guest
-
Order By Date/Time Issue
While this is more an annoyance than a problem, I'm wondering if it's something I'm doing or systematic. I'm running the query below to pull... -
Flex 2 Beta 3 date & daylight savings time issue
I have created an addDay method which takes a date as a parameter and then adds 34 * 60 * 60 * 1000 to it, creates a new date and returns it, but... -
CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
I am running a W2k SP4 box that has been upgraded from CFMX6 to CFMX7.0.1. The CFMX7.0.1 server is showing the date on the Server Settings >... -
Convert date/time to date in SQL Server 2000 statement
Can this be done? tia -
Time/Date format and changing time to GMT
Hi All, Sorry if this is the wrong newsgroup to post into, on this topic, if so, please point me in the right direction..... Currently working... -
HMOKeefe #2 Re: Date/Time Issue
This is apparently the problem. I am using a database that was given to me and
the data type was set to text for those two fields. I have to find out if
changing the data types is going to affect a bunch of linked tables before I
move ahead with this. Thanks for the insight. I will let you know how it turns
out.
HMOKeefe Guest
-
mxstu #3 Re: Date/Time Issue
Yes, it is usually better to store dates and times in date/time columns.
However, if you cannot change the data types, most databases have functions
that will allow you to convert a valid date/time string to a data/time object.
Most databases support CAST(), but I think Access may use the CDate() function.
Check your database documentation.
mxstu Guest
-
Dan Bracuk #4 Re: Date/Time Issue
Or use string functions, given that the fields are text.
Dan Bracuk Guest
-
-
Dan Bracuk #6 Re: Date/Time Issue
What does your string look like?
What would you like a pm output to look like? 02:00 PM ? 14:00 PM?
Originally posted by: HMOKeefe
What would be an example given my code above?
Dan Bracuk Guest
-
HMOKeefe #7 Re: Date/Time Issue
the string looks like this:
EventTime: 1100
EventTimeTo: 1400
I need to output it to: 11:00A - 2:00P
HMOKeefe Guest
-
Dan Bracuk #8 Re: Date/Time Issue
<cfscript>
if (left(eventtime, 2) lt 12)
display = left(eventtime, 2) & ":" & right(eventtime, 2) & "A - ";
else
display = left(eventtime, 2)-12 & ":" & right(eventtime, 2) & " -P ";
Then do the same thing with the Eventtimeto, but concatonate the new string to
the end of the display variable.
Originally posted by: HMOKeefe
the string looks like this:
EventTime: 1100
EventTimeTo: 1400
I need to output it to: 11:00A - 2:00P
Dan Bracuk Guest
-
HMOKeefe #9 Re: Date/Time Issue
Thanks Dan. This works for now until we get the date/time issue straightened out in the database.
HMOKeefe Guest
-
mxstu #10 Re: Date/Time Issue
HMOKeefe,
Either method would work. My preference is to cast the text value as
date/time objects in sql instead of in CF, so it would require only a simple
query change once the columns were converted to date/times.
Another method I've used is to create a "view" (ie. a virutual table) with the
text values already formatted as date/time values and running my select
statements on the "view" instead. Once the column datatypes are changed, I
just point the query back to the table instead of the "view".
Good Luck!
mxstu Guest
-
HMOKeefe #11 Re: Date/Time Issue
This has gotten a bit more complicated than I imagined. I have attached some
code to show you where I am with this since I needed to reference several
tables in the database to get the time formatting that I needed from the
application I am working with. Everything works with this code. Except I cannot
get the times for individual cells, instead time is replicated across the three
cells from the correct first cell. I suspect I need to do a CFLOOP but I am not
very familiar with it yet.
<cfquery name="eventList" datasource="Functions">
SELECT TOP 3 EventDate,Title, OneLineDesc, Description, EventTime,
EventTimeTo, SubCategory, Name
FROM Events5051, EventsSubCategories5051
WHERE Events5051.SubCategoryID=EventsSubCategories5051.S ubCategoryID
AND EventDate>= #CreateODBCDate(now())#
ORDER BY EventDate ASC
</cfquery>
<cfquery name="getStartTime" datasource="Functions">
SELECT *
FROM DateTimeFormat
WHERE TimeValue='#eventList.EventTime#'
</cfquery>
<cfquery name="getEndTime" datasource="Functions">
SELECT *
FROM DateTimeFormat
WHERE TimeValue='#eventList.EventTimeTo#'
</cfquery>
<cfset varTimeStart = #getStartTime.TimeDisplay#>
<cfset varTimeStop = #getEndTime.TimeDisplay#>
<body>
<table width="96%" border="0" align="center" cellpadding="2"
bgcolor="#6666FF">
<tr>
<td><span class="style3">Upcoming Seminars and Events
</span></td>
</tr>
</table>
<table width="96%" border="0" align="center" cellpadding="2" >
<tr>
<cfoutput query="eventList">
<td align="left" valign="top" > <span class="style2">#eventList.SubCategory#
</span>
<p class="style2">#LSDateFormat(eventList.EventDate,' DDDD, MMMM DD,
YYYY')#<br />
#varTimeStart# - #varTimeStop#<br >
#eventList.Description#</p>
<p class="style2">#eventList.OneLineDesc#</p>
<p class="style2">"#eventList.Title#"</p>
<p class="style2">Organized by #eventList.Name#</p> </td>
</cfoutput>
HMOKeefe Guest
-
mxstu #12 Re: Date/Time Issue
Why 3 separate queries? What is the relationship between the event tables and
the [DateTimeFormat] table? Also, it is not clear which columns in the
"eventList" query belong to which table. When joining multiple tables, you
should really specify the table name (or alias) for all columns. It makes your
code more readable and is a little less work for the database.
mxstu Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
