×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

oracle date and coldfusion date

本文发表在 rolia.net 枫下论坛This is a pretty funny story:
I'm using ColdFusion and Oracle as db server.
in my application.cfm I set db date format as this.

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY HH24:MI:SS'


then when I insert '03/18/02' into a table column(date type),
oracle take it as 03/18/0002, this is fine still although it's already becoming funny.
The joke is here:
when you get this date out and use couldfuion
dateformat(date_value, "MM/DD/YYYY") to show on the page,
It's 03/18/2002 now.

That means, acturally in oracle, you have 03/18/0002, but you can see only 03/18/2002 on the page.

even more than this, when I use sql plus or other tools, most of the tools will show only
03/18/02 or change this date to 03/18/2002.

It gave me a realy hard time to find out what happend in the system.
Cause before you realize this is the problem, you can't find out how come what you can see
03/18/2002 and another 03/18/2002 is not the same day.....


Don't know who had this before, maybe there's some solutions already.

Peter.更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / oracle date and coldfusion date
    本文发表在 rolia.net 枫下论坛This is a pretty funny story:
    I'm using ColdFusion and Oracle as db server.
    in my application.cfm I set db date format as this.

    ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY HH24:MI:SS'


    then when I insert '03/18/02' into a table column(date type),
    oracle take it as 03/18/0002, this is fine still although it's already becoming funny.
    The joke is here:
    when you get this date out and use couldfuion
    dateformat(date_value, "MM/DD/YYYY") to show on the page,
    It's 03/18/2002 now.

    That means, acturally in oracle, you have 03/18/0002, but you can see only 03/18/2002 on the page.

    even more than this, when I use sql plus or other tools, most of the tools will show only
    03/18/02 or change this date to 03/18/2002.

    It gave me a realy hard time to find out what happend in the system.
    Cause before you realize this is the problem, you can't find out how come what you can see
    03/18/2002 and another 03/18/2002 is not the same day.....


    Don't know who had this before, maybe there's some solutions already.

    Peter.更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • a bit of confusion on what you're talking about, as far as date format concerned, all the systems have proper date convert functionality, all you have to do is
      get familiar with the fact that how the date stored internally, say, date in oracle, stores date and time, so when you compare 2 same dates, don't forget the time portion, if you really got a hard time, try to use string as a intermediate variable, and use convert functions to the proper database format, don't confuse yourself before you confuse the machine.