Right, and my
point was (though not stated well :) ) was that it sounded to me like
mark was storing his dates as strings instead of using the proper data
type and letting the engine handle how best to store it internally.
Chris
David L. Penton wrote:
Not to
split hairs here, but most modern databases hold date/time/datetime
values as either integers or double precision values. What you are
talking about is the *rendered date format* and in most cases ISO8601
is the best rendered format to use (actually, I can't think of any
rdbms out there OTTOMH that don't).
Keep in mind that many settings for date/time like this are either
controlled at the database server level, or through regional settings
on the actual operating system.
For example, here is an excerpt from SQL Server Books Online entry for
datetime and smalldatetime:
<quote>
Values with the datetime data type are stored internally by Microsoft
SQL Server as two 4-byte integers. The first 4 bytes store the number
of days before or after the base date, January 1, 1900. The base date
is the system reference date. Values for datetime earlier than January
1, 1753, are not permitted. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less
precision than datetime. SQL Server stores smalldatetime values as two
2-byte integers. The first 2 bytes store the number of days after
January 1, 1900. The other 2 bytes store the number of minutes since
midnight. Dates range from January 1, 1900, through June 6, 2079, with
accuracy to the minute.
</quote>
Diving into the smalldatetime for a minute, think about the range of a
2 byte integer (assume it is unsigned)
0 - FFFF (0 to 65535)
If zero is 1900-01-01, add 65535 days to that. You get:
select dateadd(day, 65535, '1900-01-01')
2079-06-06
The upper limit of days for the small datetime value.
Now, in SQL Server's case the full range of datetime doesn't reflect
the full range of a 4 byte integer, but the concept is the same.
- dave
Christopher Jordan wrote:
Also, using the proper Date/Timestamp/etc.
data type eliminates problems with consistency across tables. One table
might hold a date as MM/DD/YY while another as MM/DD/YYYY and another
as YYYY/MM/DD or YY-MM-DD or MM-DD-YYYY or... etc... you get the idea.
:)
Chris
Christopher Jordan wrote:
Mark,
As a general rule, I always store dates as date types (or timestamps)
rather than as string representations of dates (YYYY-MM-DD) or even as
numeric representations of dates (YYYYMMDD)... it's helpful when you
want to do date comparisons and such that your dates will already be
formatted correctly, and ColdFusion will already be able to understand
them properly.
That is, of course, just my opinion. :)
Chris
Dave Shuck wrote:
Mark, have you tried:
WHERE .... duedate=#CreateODBCDate(duedate)#
or even better...
WHERE .... duedate = <cfqueryparam type="cf_sql_date"
value=#CreateODBCDate(duedate)# />
~d
On 6/6/06, *Mark Armstrong* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Sorry, MSSQL 7. table, meaning the "tasks" table that I am storing
the data.
On Jun 6, 2006, at 1:27 PM, Matt Woodward wrote:
> When you say "in the table" what specifically do you mean?
What
> database are you using?
>
> On 6/6/06, Mark Armstrong <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
>> OK,
>>
>> I give up!!! I cannot seem to get the date to work
correctly. (to
>> insert, modify or output the data in the table).
>>
>> I am CF formatting the date, properly, but its not showing
up
in the
>> table anything other than 01/01/01.
>>
>> When I modify the form to insert the new values, its still
the
>> same... what can I be doing wrong?
>>
>> The output page is this:
#DateFormat(duedate,"MM-DD-YY")#
>>
>> the modify page looks like this:
>>
>> <cfset duedate1 = DateFormat(#duedate#, "mm/dd/yy")>
>>
>> <input name="duedate" type="text" value="#duedate1#"
>> cfsqltype="CF_SQL_DATE" />
>>
>> and the modify page posts to the update processing page:
>>
>> UPDATE tasks
>> SET taskname = '#taskname#', taskdescription =
'#taskdescription#',
>> first_name='#first_name#', weburl='#weburl#',
test='#test#',
>> visual='#visual#', comments='#comments#',
duedate=#duedate#
>> WHERE taskID = #form.taskID#
>>
>> Any help is appreciated!
>>
>> Mark
_______________________________________________
Reply to DFWCFUG: [email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
|
