2 Replies Latest reply on Apr 3, 2007 5:04 AM by Johnny852

    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?
        • 1. Re: Type of object from a database date/time column
          Level 7
          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).
          • 2. Re: Type of object from a database date/time column
            Johnny852 Level 1
            Great thanks for a quick answer. ;-)