Type of object from a database date/time column

[Johnny852]

Hello everyone,

I've got a concern regarding the way in which coldfusion is treating date/time columns in a database.
In my opinion when I select a column that is let's say timestamp type - the object in a query is CF's date/time object. I've checked the query by getMetaData() and it looks like each column has its own database type (eq. varchar2, date, etc).

My concern comes from a fact that I've heard that Coldfusion is treating the date-time columns in a database as strings and in each case it parses them. So the difference in a locale between a database server and a Coldfusion server may result in a wrong date/time object.

Example:
Locale of database EU: yyyy/mm/dd
Locale of Coldfusion US: yyyy/dd/mm
The date like 1st of February 2007 stored in a database would look then in Coldfusion like: 2nd of January 2007

Could someone please confirm which version is valid?

[Newsgroup_User]

Johnny852 wrote:
> In my opinion when I select a column that is let's say timestamp type - the
> object in a query is CF's date/time object. I've checked the query by
> getMetaData() and it looks like each column has its own database type (eq.
> varchar2, date, etc).

if you already know cf is returning a datetime from a datetime in the db, what's
the question?

> My concern comes from a fact that I've heard that Coldfusion is treating the

"fact"? unless the datetime is stored as a string, that's not a "fact".

> Example:
> Locale of database EU: yyyy/mm/dd
> Locale of Coldfusion US: yyyy/dd/mm

btw neither of your example "locales" are in fact locales.

> The date like 1st of February 2007 stored in a database would look then in
> Coldfusion like: 2nd of January 2007

if you pass a numeric string representation (2/1/2007 for instance) of a date to
cf, it will be interpreted via the cf server's "default" local (ie en_US,
month/day/year) unless you use one of the LS functions like LSParseDateTime() &
specifically set the the locale (like setLocale("th_TH")) or make the date
non-ambiguous (2-feb-2007) or build the date yourself from user input using
createDate() or createDateTime(). you just need to convert the user's string
representation to a valid cf datetime object (or i guess force to database to
follow the user's locale date mask).

[Johnny852]

Great thanks for a quick answer. 😉