Thread: dateformat issue

Hi Everyone,

I have a question about dates, please.

I am using Coldfusion and Postgres
I have a function that takes the ate entered in a form and converts it  
into an odbc date format.

So the string ends up being;
{d '2008-04-10'}

Strangely, the function is used throughout the application and causes  
no issues but on one particular template.
And this same templates is replicated for other clients - that  
seemingly have no issue .


The SQL is simply

Insert into table (datefield) values ({d '2008-04-10'})

Now, I have read the docs and do not see the {d '2008-04-10'} format  
as being a valid date data type...
But it works elsewhere in the application / on other sites - thus my  
current state of confusion.

Thanks in advance for any ideas you might have.



Please contact me if you should have any questions.

Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.

E: beau@palcare.com.au
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au

On Thursday 10 April 2008 03:14, Gavin 'Beau' Baumanis wrote:
> Hi Everyone,
>
> I have a question about dates, please.
>
> I am using Coldfusion and Postgres
> I have a function that takes the ate entered in a form and converts it
> into an odbc date format.
>
> So the string ends up being;
> {d '2008-04-10'}
>
> Strangely, the function is used throughout the application and causes
> no issues but on one particular template.
> And this same templates is replicated for other clients - that
> seemingly have no issue .
>
>
> The SQL is simply
>
> Insert into table (datefield) values ({d '2008-04-10'})
>

Hi,

The above is not a valid value for a date field. What function do you use for
the conversion ?

#DateFormat(form.date, "YYYY-MM-DD")# ? Or perhaps a custom function /
conversion script ?

Which client (browser?) / platform produces the error ?

And just out of general interest, which cf-version and platform are you
using ? Pg version ?

I use pg 8.x's on CentOS and Fedora with CF 5 Pro Linux and CFMX7 Standard. I
also heard that CFMX7+ would install and run ok on Ubuntu.

Best regards,

--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---

Hi Aarni,

just so you know I am using the CF built-in function,
createodbcdate

here is the info from livedocs.

CreateODBCDate
Description

Returns a date in ODBC date format.
Category

Date and time functions
Syntax

CreateODBCDate(date)

so I pass in 2008-01-23
and I get back

{d '2008-01-23'}

The weirdness for me is that it works elsewhere in the application, I
am really stumped.

-Gavin.



On 10/04/2008, at 4:17 PM, Aarni Ruuhimäki wrote:

> On Thursday 10 April 2008 03:14, Gavin 'Beau' Baumanis wrote:
>> Hi Everyone,
>>
>> I have a question about dates, please.
>>
>> I am using Coldfusion and Postgres
>> I have a function that takes the ate entered in a form and converts
>> it
>> into an odbc date format.
>>
>> So the string ends up being;
>> {d '2008-04-10'}
>>
>> Strangely, the function is used throughout the application and causes
>> no issues but on one particular template.
>> And this same templates is replicated for other clients - that
>> seemingly have no issue .
>>
>>
>> The SQL is simply
>>
>> Insert into table (datefield) values ({d '2008-04-10'})
>>
>
> Hi,
>
> The above is not a valid value for a date field. What function do
> you use for
> the conversion ?
>
> #DateFormat(form.date, "YYYY-MM-DD")# ? Or perhaps a custom function /
> conversion script ?
>
> Which client (browser?) / platform produces the error ?
>
> And just out of general interest, which cf-version and platform are
> you
> using ? Pg version ?
>
> I use pg 8.x's on CentOS and Fedora with CF 5 Pro Linux and CFMX7
> Standard. I
> also heard that CFMX7+ would install and run ok on Ubuntu.
>
> Best regards,
>
> --
> Aarni Ruuhimäki
> ---
> Burglars usually come in through your windows.
> ---
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Please contact me if you should have any questions.

Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.

E: beau@palcare.com.au
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au

On Thursday 10 April 2008 09:41, Gavin 'Beau' Baumanis wrote:
> Hi Aarni,
>
> just so you know I am using the CF built-in function,
> createodbcdate
>
> here is the info from livedocs.
>
> CreateODBCDate
> Description
>
> Returns a date in ODBC date format.
> Category
>
> Date and time functions
> Syntax
>
> CreateODBCDate(date)
>
> so I pass in 2008-01-23
> and I get back
>
> {d '2008-01-23'}
>
> The weirdness for me is that it works elsewhere in the application, I
> am really stumped.
>
> -Gavin.
>

That value shouldn't work at all ... ?

<cfoutput>
#Now()# ||

#CreateODBCDate(2008-04-10)# = #CreateODBCDate(1994)#

|
#CreateODBCDate('2008-04-10')#
|
#CreateODBCDate(Now())#
|
#DateFormat(Now(), "YYYY-MM-DD")#
|
#DateFormat('2008-04-10', "YYYY-MM-DD")#
</cfoutput>

Produces:
{ts '2008-04-10 10:26:21'} || {d '1905-06-16'} = {d '1905-06-16'} | {d
'2008-04-10'} | {d '2008-04-10'} | 2008-04-10 | 2008-04-10

This is on CFMX7.

Best regards,

--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---

Gavin 'Beau' Baumanis escribió:
> Hi Everyone,
>
> I have a question about dates, please.
>
> I am using Coldfusion and Postgres
> I have a function that takes the ate entered in a form and converts it 
> into an odbc date format.
>
> So the string ends up being;
> {d '2008-04-10'}
>
> Strangely, the function is used throughout the application and causes 
> no issues but on one particular template.
> And this same templates is replicated for other clients - that 
> seemingly have no issue .
>
>
> The SQL is simply
>
> Insert into table (datefield) values ({d '2008-04-10'})
>
> Now, I have read the docs and do not see the {d '2008-04-10'} format 
> as being a valid date data type...
> But it works elsewhere in the application / on other sites - thus my 
> current state of confusion.
>
> Thanks in advance for any ideas you might have.
>
>
>
> Please contact me if you should have any questions.
>
> Gavin 'Beau' Baumanis
> Senior Application Developer
> PalCare Pty. Ltd.
>
> E: beau@palcare.com.au
> T: +61 -3 9318 4567
> M: +61 -4 38 545 586
> W: http://www.palcare.com.au
>   I had some similar probles with dates, and i solved it using a 
string format (yyyymmdd) to set date format, and two functions (toString 
and toDate) made by myself inf the necesary language (asp, php, jsp....)
   This format is full valid for comparations (>, <, =) and orders in 
SQL. The only problem is to work in your application, but it may be 
solved with some functions
  

Hi,

Gavin 'Beau' Baumanis <gavinb@eclinic.com.au> writes:
> I have a question about dates, please.
>
> I am using Coldfusion and Postgres
> I have a function that takes the ate entered in a form and converts it
> into an odbc date format.
>
> So the string ends up being;
> {d '2008-04-10'}
>
> Strangely, the function is used throughout the application and causes
> no issues but on one particular template.
> And this same templates is replicated for other clients - that
> seemingly have no issue .
>
>
> The SQL is simply
>
> Insert into table (datefield) values ({d '2008-04-10'})
>
> Now, I have read the docs and do not see the {d '2008-04-10'} format
> as being a valid date data type...
> But it works elsewhere in the application / on other sites - thus my
> current state of confusion.
>
> Thanks in advance for any ideas you might have.

As others have underlined, "{d '2008-04-10'}" is not a valid SQL
timestamp format. I think your query adapter somehow converts it to its
appropriate format on the fly. If I were you, to spot the problem, I'd
turn query logging on in the server side and see

- How does working date queries get passed to the server?
- How does bogus query get passed to the server?

This details can help you/us to identify the problem.


Regards.