MySQL date problem
I have an app that I have created using CF 9.01, MySQL 5.1.41 and ORM. One of the properties of my object is a start date. Everything inserts and updates to the MySQL database without a problem. This issue that I am having is when I try to perform any CF date functions on the stored date. If I try to do a datecompare or datediff function on the start date, I get a Date value passed to date function DateCompare is unspecified or invalid error message. However, if I try either isValid(date,Deal.getStartDate()) or isDate(Deal.getStartDate()), both return true. Any thoughts? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349600 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL date problem
Nevermind. Stupid mistake. I was looping over a number of records and it turns out that one of the records had a null value set which was what caused the error. Had nothing to do with MySQL dates at all. Ugh. I have an app that I have created using CF 9.01, MySQL 5.1.41 and ORM. One of the properties of my object is a start date. Everything inserts and updates to the MySQL database without a problem. This issue that I am having is when I try to perform any CF date functions on the stored date. If I try to do a datecompare or datediff function on the start date, I get a Date value passed to date function DateCompare is unspecified or invalid error message. However, if I try either isValid(date, Deal.getStartDate()) or isDate(Deal.getStartDate()), both return true. Any thoughts? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL date problem
can you perform any date functions on it at all ? Try running it through CreateDate() or CreateODBCDate() first and see if that helps On Sun, Jan 22, 2012 at 8:48 PM, Dean Lawrence dean...@gmail.com wrote: I have an app that I have created using CF 9.01, MySQL 5.1.41 and ORM. One of the properties of my object is a start date. Everything inserts and updates to the MySQL database without a problem. This issue that I am having is when I try to perform any CF date functions on the stored date. If I try to do a datecompare or datediff function on the start date, I get a Date value passed to date function DateCompare is unspecified or invalid error message. However, if I try either isValid(date,Deal.getStartDate()) or isDate(Deal.getStartDate()), both return true. Any thoughts? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344807
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Baffing date problem
Kevin, Try this. select * from qEvents where DateDiff(d, qEvents.TheDate, #dayview#) = 0 -- Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: Free ColdFusion developer hosting www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
Quotes around #dayview# ?
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = '#dayview#'
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: 22 May 2011 03:16
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the
same despite
the 0 difference but the query returns no rows and I'm stumped. A
cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly
appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344809
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
Thank you - that looks elegant - but that took a dive too -
Encountered DateDiff ( d ,. Incorrect conditional expression, Expected one
of [like|null|between|in|comparison] condition
The query looks like SQL
select * from qEvents where DateDiff(d, qEvents.TheDate, {ts '2011-06-01
00:00:00'}) = 0
you can see it here if it helps
http://www.trsbok.com/_ncsi/index.cfm?month=6year=2011
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Russ Michaels [mailto:r...@michaels.me.uk]
Sent: Sunday, 22 May 2011 12:24
To: cf-talk
Subject: Re: Baffing date problem
Kevin,
Try this.
select *
from qEvents
where DateDiff(d, qEvents.TheDate, #dayview#) = 0
--
Russ Michaels
www.bluethunderinternet.com : Business hosting services solutions
www.cfmldeveloper.com: Free ColdFusion developer hosting
www.michaels.me.uk : my blog
www.cfsearch.com : ColdFusion search engine
**
*skype me* : russmichaels
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344810
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
I tried that - but I'll have another crack - thank you!!!
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk]
Sent: Sunday, 22 May 2011 12:33
To: cf-talk
Subject: RE: Baffing date problem
Quotes around #dayview# ?
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = '#dayview#'
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: 22 May 2011 03:16
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the
same despite
the 0 difference but the query returns no rows and I'm stumped. A
cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly
appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344811
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
First id try something like:
Where qEvents.theDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay# /
If that doesn't work, Id try:
where qEvents.TheDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#createODBCDateTime(dayview)# /
If that doesn't work... read on.
If you are on SQL Server 2008, you could try something like...
where cast(qEvents.TheDate as date) = #createODBCDate(dayview)#
Other wise one of these might work...
Where cast(datePart(year, qEvents.TheDate) as nvarchar(4)) + '/' +
cast(datePart(month, qEvents.TheDate) as nvarchar(2)) + '/' +
cast(datePart(day, qEvents.TheDate) as nvarchar(2)) = #dateformat(dayview,
-mm-dd)#
Or maybe something goofy like...
Where qEvents.theDate = '#dayview# 00:00:00.0'
.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: Saturday, May 21, 2011 10:16 PM
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344812
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Baffing date problem
you need to make the same format on both sides of the SQL where clause
so try:
cfset dayview = #createODBCDate(createdate(year, month, thisday))#
On 22/05/2011 12:15, Kevin Parker wrote:
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
--
Yours,
Kym Kovan
mbcomms.net.au
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344813
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Baffing date problem
Thanks Bobby (and the other good folk who chipped in too) - your first
option did the trick.
Where qEvents.theDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay#
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
-Original Message-
From: Bobby Hartsfield [mailto:bo...@acoderslife.com]
Sent: Sunday, 22 May 2011 12:41
To: cf-talk
Subject: RE: Baffing date problem
First id try something like:
Where qEvents.theDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#thisDay# /
If that doesn't work, Id try:
where qEvents.TheDate = cfqueryparam cfsqltype=cf_sql_timestamp
value=#createODBCDateTime(dayview)# /
If that doesn't work... read on.
If you are on SQL Server 2008, you could try something like...
where cast(qEvents.TheDate as date) = #createODBCDate(dayview)#
Other wise one of these might work...
Where cast(datePart(year, qEvents.TheDate) as nvarchar(4)) + '/' +
cast(datePart(month, qEvents.TheDate) as nvarchar(2)) + '/' +
cast(datePart(day, qEvents.TheDate) as nvarchar(2)) = #dateformat(dayview,
-mm-dd)#
Or maybe something goofy like...
Where qEvents.theDate = '#dayview# 00:00:00.0'
.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Kevin Parker [mailto:tras...@internode.on.net]
Sent: Saturday, May 21, 2011 10:16 PM
To: cf-talk
Subject: Baffing date problem
I love CF but dates are definitely my Achilles heel.
I've dumped qEvents.TheDate and its formatted like {ts '2011-06-07
00:00:00.0'}
I've dumped dayview and its formatted like {ts '2011-06-07 00:00:00'}
I would have thought that as time stamps CF would see these the same despite
the 0 difference but the query returns no rows and I'm stumped. A cfdump of
qEvents shows the query is returning data correctly. Funny thing is this
works on a CF6 box running SQL2000 but fails on a CF8 box running SQL2005.
What I'm trying to do is do a check for qEvents that on a given day there
are or are not events for that day (thisday). Any advice greatly appreciated
- thank you.
cfset dayview = #createdate(year, month, thisday)#
cfquery name=ForToday dbtype=query
select *
from qEvents
where qEvents.TheDate = #dayview#
/cfquery
++
Kevin Parker
Advanced Imaging
e: webmas...@advancedimaging.com.au
w: www.advancedimaging.com.au
m: 0418 815 527
++
http://au.linkedin.com/in/krparker
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344814
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: date problem
Hi Calude, thanks for the relies, however the data is being transferred from ms excel and sometimes comes in the EU format and sometimes in the US format. i suppose the only way we can do this is to force the users to put it into eu format and run it through your tag to see if it is a valid date format for eu thanks richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297803 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
But this is not really 100% proof, since some dates may be valid in US format, although they were meant in EU format. Take 10/11/2008 for instance. So the date may be valid and still not be the good one. To be 100% sure, you could consider a date pick up system. hmmm very good point, thanks, what is a date pickup system? richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297554 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: date problem
For instance: http://www.garrett.nildram.co.uk/calendar/scw.htm a javascript which works nicely for me. Needs a couple of hours to figure it out. -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Monday, January 28, 2008 10:41 AM To: CF-Talk Subject: Re: date problem Richard White wrote: But this is not really 100% proof, since some dates may be valid in US format, although they were meant in EU format. Take 10/11/2008 for instance. So the date may be valid and still not be the good one. To be 100% sure, you could consider a date pick up system. hmmm very good point, thanks, what is a date pickup system? i believe Claude meant a date picker, like a pop-up calendar you can click on a date in and it will populate your date input field for you with the chosen date in a pre-defined format. --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297583 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
Richard White wrote: But this is not really 100% proof, since some dates may be valid in US format, although they were meant in EU format. Take 10/11/2008 for instance. So the date may be valid and still not be the good one. To be 100% sure, you could consider a date pick up system. hmmm very good point, thanks, what is a date pickup system? i believe Claude meant a date picker, like a pop-up calendar you can click on a date in and it will populate your date input field for you with the chosen date in a pre-defined format. --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297582 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
date pick up system - is that a fancy way to say car? :-) hmm.. come to think of it, date picker does not sound much better... conjures images of some random number system... hmm... maybe it's just me... --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com Claude Schneegans wrote: i believe Claude meant a date picker That's it! Date picker! ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297622 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
i believe Claude meant a date picker That's it! Date picker! ;-) You can pick your friends and you can pick your date, but you can't pick your date's friends. Bachelor number 2, if you were a fruit, what fruit would you be? -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297624 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
i believe Claude meant a date picker That's it! Date picker! ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297621 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date problem
Oohh, Oohh, Oooh, I know, I know Dennis Rodman... Bachelor number 2, if you were a fruit, what fruit would you be? -- William E. Seiter Have you ever read a book that changed your life? Go to: www.winninginthemargins.com Enter passkey: goldengrove Web Developer http://William.Seiter.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297625 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
Oohh, Oohh, Oooh, I know, I know Dennis Rodman... Bachelor number 2, if you were a fruit, what fruit would you be? Now that you mention it, the hair does make him look a bit fruity... in a totally heterosexual way (Clerks II). Don't ask. My mind is a strange place. :P -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 503.236.3691 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297629 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
:) thanks again this is very straight forward and at the moment is solving our problems. sometimes we dont know what format the user is going to pass the date in so we first check with input mask US and if it says invalid date then we pass in input mask EU and if it works then fine and if not then there is an error in their date :) so this is working well for us! thanks again claude ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297541 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
so we first check with input mask US and if it says invalid date then we pass in input mask EU and if it works Good point. But this is not really 100% proof, since some dates may be valid in US format, although they were meant in EU format. Take 10/11/2008 for instance. So the date may be valid and still not be the good one. To be 100% sure, you could consider a date pick up system. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297542 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
Hi Claude, i just bought this component, it seems fairly straight forward, thanks very much this has been an ongoing issue for some time!!! will let you know if i have any questions if you dont mind thanks again for all your help claude richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297525 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
thanks again for all your help claude You're welcome, and congratulation for you wise shopping ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297527 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
07/24/1978 is an invalid date format Ok, now I see what your problem is: If you're using LSdateFormat, the function (and here is the difference with dateFaormat) expect a date. Since 07/24/1978 is not a date, but a string, CF will try to convert it to a date, but according the English (UK) standard, which is dd/mm/. Thus 24 is interpreted as the month, this is what causes the error. If you use only dateFormat, the date can be interpreted as an American date and it works. You only have two choices: 1. use createDate and pass independently the day, month, year parts as substrings of the date. 2. spend a few bucks, save lots of time and hurry to get CF_convertDate here: http://www.contentbox.com/claude/customtags/convertDate/viewConvertDate.cfm With only one line of code, you will solve your problem: CF_convertDate inputVariable=myDate inputMask=US outputMask=EU or in function call format: CFSET outputDate = convertDate(myDate, string, string, US, EU) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297491 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
Hi claude, thanks for this, i am starting to understand the problem now, i will however take a look at your function as the lsdateformat is starting to give me nightmares!!! thanks claude richard ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297509 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date problem
Richard Just a final comment on this: If you know that your dates are always in the /mm/dd format, then you should use parseDateTime() to parse them and then format accordingly: Eg. Assuming your date is in a variable 'd' Format according to the setLocale() value: cfset dateDisplay = lsDateFormat(parseDateTime(d)) Format explicitly: cfset dateDisplay = dateFormat(parseDateTime(d),dd/mm/) Regards Kevan -Original Message- From: Kevan Stannard [mailto:[EMAIL PROTECTED] Sent: Saturday, 26 January 2008 2:28 PM To: 'cf-talk@houseoffusion.com' Subject: RE: date problem Hi Richard We deal with the same issue here in Australia. You may have the problem sorted out by now, but you may like to have a look at some notes I put down a little while ago on this. http://stannard.net.au/blog/index.cfm/2006/10/25/Date-Objects-vs-Date-String s-in-ColdFusion Just an extra comment: When you use lsDateFormat() then CF uses lsParseDateTime() to first convert your string to a date object before formatting. When you use dateFormat() then CF uses parseDateTime() to first convert your string to a date object before formatting. parseDateTime() can handle dates in /mm/dd format lsParseDateTime() can not handle dates in /mm/dd format, which is why you get an error. Kevan -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Monday, 21 January 2008 1:18 AM To: CF-Talk Subject: date problem Hi, i have been playing around with date formats for ages and still encountering problems. i am in the uk and have set the following code when someone logs in: cfset SetLocale(English (UK)) i have dates stored in a mysql db which is in the format /mm/dd when i try to get data out of the db and put it in the dd/mm/ format i use lsdateformat(date,dd/mm/) and it spits out an error: 01/20/2008 is not a valid date format!! i dont understand this at all, does anyone have any ideas why it would do this. i dont understand why it would output this when i am telling it to put it in dd/mm/ format not mm/dd/ format thanks very much ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297512 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date problem
Hi Richard We deal with the same issue here in Australia. You may have the problem sorted out by now, but you may like to have a look at some notes I put down a little while ago on this. http://stannard.net.au/blog/index.cfm/2006/10/25/Date-Objects-vs-Date-String s-in-ColdFusion Just an extra comment: When you use lsDateFormat() then CF uses lsParseDateTime() to first convert your string to a date object before formatting. When you use dateFormat() then CF uses parseDateTime() to first convert your string to a date object before formatting. parseDateTime() can handle dates in /mm/dd format lsParseDateTime() can not handle dates in /mm/dd format, which is why you get an error. Kevan -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Monday, 21 January 2008 1:18 AM To: CF-Talk Subject: date problem Hi, i have been playing around with date formats for ages and still encountering problems. i am in the uk and have set the following code when someone logs in: cfset SetLocale(English (UK)) i have dates stored in a mysql db which is in the format /mm/dd when i try to get data out of the db and put it in the dd/mm/ format i use lsdateformat(date,dd/mm/) and it spits out an error: 01/20/2008 is not a valid date format!! i dont understand this at all, does anyone have any ideas why it would do this. i dont understand why it would output this when i am telling it to put it in dd/mm/ format not mm/dd/ format thanks very much ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297511 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
Hi, none of these suggestions are working we have the set locale setup in the application page, we have even tried to put it in the onrequest function cfset SetLocale(English (UK)) then we are simply trying to run this following code to test it and it is not working: cfset dateV = lsdateformat(07/24/1978, dd/mm/) the error is: 07/24/1978 is an invalid date format does anyone have any other ideas? thanks for any help ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297483 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
On Monday 21 Jan 2008, Azadi Saryev wrote: if it is same for dateformat() and lsdateformat(), why would they return different results then? Because those functions do different things. -- Tom Chiverton Helping to seamlessly mesh real-time mindshares on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296961 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
thanks very much for your replies and discussions i will try this and see if it works thanks again ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296962 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
yes, obviously. --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com Tom Chiverton wrote: On Monday 21 Jan 2008, Azadi Saryev wrote: if it is same for dateformat() and lsdateformat(), why would they return different results then? Because those functions do different things. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297043 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
date problem
Hi, i have been playing around with date formats for ages and still encountering problems. i am in the uk and have set the following code when someone logs in: cfset SetLocale(English (UK)) i have dates stored in a mysql db which is in the format /mm/dd when i try to get data out of the db and put it in the dd/mm/ format i use lsdateformat(date,dd/mm/) and it spits out an error: 01/20/2008 is not a valid date format!! i dont understand this at all, does anyone have any ideas why it would do this. i dont understand why it would output this when i am telling it to put it in dd/mm/ format not mm/dd/ format thanks very much ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296918 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
hi, i have just tried it by using dateformat instead of lsdateformat and it worked fine. i dont understand, does anyone know what the lsdateformat really does and why the lsdateformat would now work but the dateformat did thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296920 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
i have just tried it by using dateformat instead of lsdateformat and it worked fine. Again, WHERE does is work, and WHERE doesn'it ? If it is in an UPDATE or INSERT SQL statement, it may work, or not, depending of the way CF will interpret the data as a date. In SQL, you should always use CreateODBCDate or CreateODBCDateTime, never submit a date as a formated string. See: http://www.contentbox.com/claude/customtags/convertDate/viewConvertDate.cfm -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296923 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
i have dates stored in a mysql db which is in the format /mm/dd First of all, unless your date field has text type and you store the date as a string, the db format is not /mm/dd. It is some floating value specific to the database. Some early db systems would store it as a string MMDD, like dBase, Foxpro, Clipper, etc. when i try to get data out of the db and put it in the dd/mm/ format i use lsdateformat(date,dd/mm/) and it spits out an error: 01/20/2008 is not a valid date format!! Show us some code, especially the line that spits ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296922 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
Hi Claude, we use mysql 5.0 and it does store the date as /mm/dd. we run a query to get the date out. then its just the following piece of code that doesnt work: cfset dateOfBirth = lsdateformat(queryName.dateofbirth,dd/mm/) it 'spits' out the error stated above 'invalid date format 01/16/1990' it is saved in the db as 1990/01/16, im not telling it to put it in the format 01/16/1990, i am telling it to put it in the format 16/01/1990 if i put exactly the same code but take out the ls then it works fine cfset dateOfBirth = dateformat(queryName.dateofbirth,dd/mm/) thanks for your help ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296924 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date problem
then its just the following piece of code that doesnt work: cfset dateOfBirth = lsdateformat(queryName.dateofbirth,dd/mm/) it 'spits' out the error stated above 'invalid date format 01/16/1990' it is saved in the db as 1990/01/16, im not telling it to put it in the format 01/16/1990, i am telling it to put it in the format 16/01/1990 if i put exactly the same code but take out the ls then it works fine cfset dateOfBirth = dateformat(queryName.dateofbirth,dd/mm/) Have you set your locale? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296928 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
we use mysql 5.0 and it does store the date as /mm/dd. This this actually how your database will *display* the date, but internally, it is stored as a 3 bytes integer value. (see http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html ) But are you sure the field is defined as a *date* type, or as a string? Anyway, CF should be able to recognize the format even if it is passed as a string from the query. I'v tried this code, and it works wit no problem under CF 5 ans CF 7 : cfset SetLocale(English (UK)) CFSET date1 = 16/01/1990 CFSET date2 = 01/16/1990 CFSET date3 = 1990/01/16 CFOUTPUT dateFormat(date1, dd/mm/) = #dateFormat(date1, dd/mm/)#BR dateFormat(date2, dd/mm/) = #dateFormat(date1, dd/mm/)#BR dateFormat(date3, dd/mm/) = #dateFormat(date1, dd/mm/)#BR LSdateFormat(date1, dd/mm/) = #LSdateFormat(date1, dd/mm/)#BR LSdateFormat(date2, dd/mm/) = #LSdateFormat(date1, dd/mm/)#BR LSdateFormat(date3, dd/mm/) = #LSdateFormat(date1, dd/mm/)#BR /CFOUTPUT if i put exactly the same code but take out the ls then it works fine Weird indeed. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296930 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: date problem
-Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, January 20, 2008 3:03 PM To: CF-Talk Subject: Re: date problem But are you sure the field is defined as a *date* type, or as a string? Anyway, CF should be able to recognize the format even if it is passed as a string from the query. I think that the dateFormat() functions will consider any string passed as a numerical representation of the date unless it's quoted... I'm not sure how that will affect this, but it might. Perhaps putting the value like this: lsDateFormat(#variable#, mask)? Something to try at least. Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296931 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
I think that the dateFormat() functions will consider any string passed as a numerical representation of the date unless it's quoted. All CF function will take ANYTHING, strings, numerics or dates (which are float values anyway), if it can be converted to a date, CF will convert it prior passing the value to the function. The only problem is that it will first try to convert it according to the American format. If you're working in American format, then this is not a problem, but for the rest of the world, there is a risk that the date in converted the wrong way. IE: 13/10/2007 will be interpreted as october 13 2007, because it is not a valid date under American format. But just one day before, it would have been interpreted as Dec. 10th 2007! This is a real time bomb. You may have an application working correctly for days, the suddenly (the 13th of each month) it will bug. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296938 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date problem
-Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, January 20, 2008 6:21 PM To: CF-Talk Subject: Re: date problem I think that the dateFormat() functions will consider any string passed as a numerical representation of the date unless it's quoted. All CF function will take ANYTHING, strings, numerics or dates (which are float values anyway), This is what I was remembering - from the docs for lsdateformat and dateformat: When passing date/time value as a string, enclose it in quotation marks. Otherwise, it is interpreted as a number representation of a date/time object. So it definitely seems like CF treats the input differently somehow. ;^) Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296944 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date problem
if it is same for dateformat() and lsdateformat(), why would they return different results then? --- Azadi Saryev Sabai-dee.com http://www.sabai-dee.com Jim Davis wrote: This is what I was remembering - from the docs for lsdateformat and dateformat: When passing date/time value as a string, enclose it in quotation marks. Otherwise, it is interpreted as a number representation of a date/time object. So it definitely seems like CF treats the input differently somehow. ;^) Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296945 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
So it definitely seems like CF treats the input differently somehow. Not really. CF is a typeless language. It is SGML compliant and HTML compatible. So by definition, EVERYTHING is passed as a string in attributes, even if there is no quotation mark. For function calls, it is a bit different: Every function expect values to be passed as numeric, date or string or object,... Like in many languages, if the type of an argument is not what is expected, the argument will be automatically converted into the correct format PRIOR to call the function. It is then an arror to think that a function like dateFormat () can receive a date as a string. It ALWAYS recives date as a date, if the argument was a string, the compiler had it converted before the function was called. This is when it becomes dangerous, because CF has to guess the original format from which it has to make the conversion, and sometimes it guesses wrong. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296949 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: date problem
-Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, January 20, 2008 10:48 PM To: CF-Talk Subject: Re: date problem So it definitely seems like CF treats the input differently somehow. Not really. CF is a typeless language. It is SGML compliant and HTML compatible. So by definition, EVERYTHING is passed as a string in attributes, even if there is no quotation mark. Just reportin' what I reads, boss! ;^) The statement in the docs isn't really clear on how the behavior differs, but it was deemed important enough to note so I assume it differs somehow. On the other hand it may not make a bit of difference. Jim Davis ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296951 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date problem
so I assume it differs somehow. Well, there is actually a difference: If you pass the date as a date to dateformat(), ie: dateFormat(now()...), there is no ambiguity. It will always work. However, if you pass it as a string, CF will have to convert it, and if you work with dates in ISO format, and do not set up your Locale correctly, you're asking for trouble. there is about 50% chance the conversion will be wrong. So better always explicitly convert the dates yourself. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296955 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: ColdFusion SQL Date problem
Donald, Did you upgrade your database from MySQL4 to MySQL5 as part of your upgrade to CF8? If so, you might check your submitDate field in your tblRegistration and see if the default still exists in the table (assuming you were using a default value to set the current date in that table). It might be that the data was copied over, but not some of the settings. Steve Hello, Recently I've noticed a weird problem. During registration, I keep customer information in a registration (temporary) table until they confirm their account via email. Once the account is confirmed, I copy their data from the registration table into the customer table. The SQL is similar to this: INSERT INTO tblCustomers (firstName, lastName, submitDate) SELECT firstName, lastName, submitDate FROM tblRegistration WHERE customerID = #getUserInfo.customerID# This has worked fine for over 400 customers so far. But recently I have noticed the submit date is copying over incorrectly and is being inserted as null (-00-00). The only thing I could think of that has changed is that Crystal Tech (the hosting company) has switched over to ColdFusion 8. Is there any obvious solution to this problem that I have missed? Could it be something that changed in ColdFusion 8? Any help would be appreciated. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290731 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: ColdFusion SQL Date problem
I am still using MySQL4 for the database. Both tables are identical besides the table name. When the data is inserted into the registration table, I use the current date in ColdFusion to put into the SQL query. When the user confirms their account, I pull their customer data by ID and simply copy it from the Registration table into the Customer table. Now that the (SSL) security certificate has been renewed on the website, that one date field is copying over perfectly fine. I cannot think of an explanation for this issue, especially since the other date fields never had a problem. Just the submitDate... Donald, Did you upgrade your database from MySQL4 to MySQL5 as part of your upgrade to CF8? If so, you might check your submitDate field in your tblRegistration and see if the default still exists in the table (assuming you were using a default value to set the current date in that table). It might be that the data was copied over, but not some of the settings. Steve Hello, Recently I've noticed a weird problem. During registration, I keep customer information in a registration (temporary) table until they confirm their account via email. Once the account is confirmed, I copy their data from the registration table into the customer table. The SQL is similar to this: INSERT INTO tblCustomers (firstName, lastName, submitDate) SELECT firstName, lastName, submitDate FROM tblRegistration WHERE customerID = #getUserInfo.customerID# This has worked fine for over 400 customers so far. But recently I have noticed the submit date is copying over incorrectly and is being inserted as null (-00-00). The only thing I could think of that has changed is that Crystal Tech (the hosting company) has switched over to ColdFusion 8. Is there any obvious solution to this problem that I have missed? Could it be something that changed in ColdFusion 8? Any help would be appreciated. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290783 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
ColdFusion SQL Date problem
Hello, Recently I've noticed a weird problem. During registration, I keep customer information in a registration (temporary) table until they confirm their account via email. Once the account is confirmed, I copy their data from the registration table into the customer table. The SQL is similar to this: INSERT INTO tblCustomers (firstName, lastName, submitDate) SELECT firstName, lastName, submitDate FROM tblRegistration WHERE customerID = #getUserInfo.customerID# This has worked fine for over 400 customers so far. But recently I have noticed the submit date is copying over incorrectly and is being inserted as null (-00-00). The only thing I could think of that has changed is that Crystal Tech (the hosting company) has switched over to ColdFusion 8. Is there any obvious solution to this problem that I have missed? Could it be something that changed in ColdFusion 8? Any help would be appreciated. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290697 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: ColdFusion SQL Date problem
ColdFusion will throw an error when if you supply -00-00 as the value for any date functions, cfqueryparam, cfargument with a type of date, etc. because -00-00 is not a valid date (neither is 02-30-2007). HTH, Aaron P.S. I had this same problem and Sean Corfield alerted me to the fact that CF is smarter than I thought... On 10/9/07, Donald Burns [EMAIL PROTECTED] wrote: Hello, Recently I've noticed a weird problem. During registration, I keep customer information in a registration (temporary) table until they confirm their account via email. Once the account is confirmed, I copy their data from the registration table into the customer table. The SQL is similar to this: INSERT INTO tblCustomers (firstName, lastName, submitDate) SELECT firstName, lastName, submitDate FROM tblRegistration WHERE customerID = #getUserInfo.customerID# This has worked fine for over 400 customers so far. But recently I have noticed the submit date is copying over incorrectly and is being inserted as null (-00-00). The only thing I could think of that has changed is that Crystal Tech (the hosting company) has switched over to ColdFusion 8. Is there any obvious solution to this problem that I have missed? Could it be something that changed in ColdFusion 8? Any help would be appreciated. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290698 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: ColdFusion SQL Date problem
I'm not supplying -00-00 exactly. The proper date shows up in the temporary table. But when copied to the customer table through the above SQL statement, it receives -00-00 (null). ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290699 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: ColdFusion SQL Date problem
Is there any chance something like this can happen if a websites SSL security certificate expires? That is one thing that happened during the time this problem occurred. But out of about 40 customer fields (two others were also even dates), that one date field was the only one that kept getting inserted as null when copied over. Now that the certificate is reinstalled, the one date field copied perfectly fine from the registration to the customer table. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290708 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
AJ Mercer wrote: could there be a database trigger on the table that is manipulating the date? Nothing. It's a straight insert. It *has* to be something related to the two different server locations, but I'll be darned if I can figure it out. For now, I'm just going to have to use DateAdd to all entered dates. What I'm worried about though, is I bet there'll be one particular time of day where it *doesn't* transpose the dates (exactly 2 minutes and 15 seconds after midnight or something stupid...) and they still get screwed up. So far, I haven't found such an anomaly yet, but it's already weird enough as it is. ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280759 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
did you get an answer to this? - Original Message From: Les Mizzell [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, June 10, 2007 7:43:28 PM Subject: Re: A Date Problem that's got me tearing out my hair! I have worked with this all weekend, and can find no rhyme or reason at all for what's happening here. I enter into my forms: start date: 6/1/2007 end date: 6/10/2007 and what gets returned is: start date: 5/31/2007 end date: 6/09/2007 I'm completely stumped! What in the hell could be causing this? Just to recap - the website with form located in Guernsey. The forms calls a webservice that uses a CFC located on a HostMySite server here in the US and writes to a database here in the US. Are the HostMysite servers located in Deleware? There's a 5 hour difference between the server time in Guernsey and Deleware. It doesn't seem to matter what time of day I enter information into a form, the problem still occurs, so I'm not sure that's got anything to do with it. Anybody got any further ideas? ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280618 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
Post the dates a day in advance. Problem solved! ;] Sorry, I know, I know... had to do it... ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280619 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A Date Problem that's got me tearing out my hair!
Les Mizzell wrote: What in the hell could be causing this? hard to diagnose w/out knowing what you're doing to the datetime objects but it appears to be a simple tz issue. what tz is the Guernsey actually in? is the Guernsey server a cf one? if so, are you manipulating the datetime on that server? on the HMS server? ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280620 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A Date Problem that's got me tearing out my hair!
Sounds like the data incoming has UTC data that is being taken into consideration before it gets to your printf statement. would something in http headers affect deserialization of values from the FORM scope before they are printable? - Original Message From: Ariel Jakobovits [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, June 10, 2007 9:39:03 PM Subject: Re: A Date Problem that's got me tearing out my hair! did you get an answer to this? - Original Message From: Les Mizzell [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Sunday, June 10, 2007 7:43:28 PM Subject: Re: A Date Problem that's got me tearing out my hair! I have worked with this all weekend, and can find no rhyme or reason at all for what's happening here. I enter into my forms: start date: 6/1/2007 end date: 6/10/2007 and what gets returned is: start date: 5/31/2007 end date: 6/09/2007 I'm completely stumped! What in the hell could be causing this? Just to recap - the website with form located in Guernsey. The forms calls a webservice that uses a CFC located on a HostMySite server here in the US and writes to a database here in the US. Are the HostMysite servers located in Deleware? There's a 5 hour difference between the server time in Guernsey and Deleware. It doesn't seem to matter what time of day I enter information into a form, the problem still occurs, so I'm not sure that's got anything to do with it. Anybody got any further ideas? ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280622 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: A Date Problem that's got me tearing out my hair!
Les, It sounds like maybe it's attempting to convert the time you entered to a local time to the server. Is the web service doing anything with dateConvert()? Are you sure you're not supposed to be entering the date/times in GMT format? -Dan -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Sunday, June 10, 2007 10:43 PM To: CF-Talk Subject: Re: A Date Problem that's got me tearing out my hair! I have worked with this all weekend, and can find no rhyme or reason at all for what's happening here. I enter into my forms: start date: 6/1/2007 end date: 6/10/2007 and what gets returned is: start date: 5/31/2007 end date: 6/09/2007 I'm completely stumped! What in the hell could be causing this? Just to recap - the website with form located in Guernsey. The forms calls a webservice that uses a CFC located on a HostMySite server here in the US and writes to a database here in the US. Are the HostMysite servers located in Deleware? There's a 5 hour difference between the server time in Guernsey and Deleware. It doesn't seem to matter what time of day I enter information into a form, the problem still occurs, so I'm not sure that's got anything to do with it. Anybody got any further ideas? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280626 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
It sounds like maybe it's attempting to convert the time you entered to a local time to the server. That was my first thought. BUT - right now (8:40 AM) it's 6/11/2007 both here and there. I've run test all hours and it happens regardless. Still, this *has* to have something to do with it, I just can't figure out WHY. I've tested with dates in the past as well. Something on the Guernsey server seems to be the cause, but jezz - what? Is the web service doing anything with dateConvert()? Nothing at all. On the Guernsey side (simplified...): cfinvoke webservice=#request.cfcLOCATION#.. WSdepDATE=#form.depDATE# ... On the US side it's a simple insert statement with the date handled thusly: cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / That's it. Now, on the US side - it's also creating/recording the date the record is inserted using now(), and *THAT* date is correct. Go figure... Are you sure you're not supposed to be entering the date/times in GMT format? I'm doing a calculation between two dates on the form, and the calculation is coming out correct - the date just end up one day off. The only solution I can actually come up with was an earlier tongue-and-cheeck just post the dates a day in advance. Still, I'd like to know *why* it's happening exactly. I've never seen anything like this before. Coffee - must have more coffee input. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280628 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
could there be a database trigger on the table that is manipulating the date? This has happened to me - and it was only on the production server, not test and dev; that had me pulling my hair out!! On 6/11/07, Les Mizzell [EMAIL PROTECTED] wrote: It sounds like maybe it's attempting to convert the time you entered to a local time to the server. That was my first thought. BUT - right now (8:40 AM) it's 6/11/2007 both here and there. I've run test all hours and it happens regardless. Still, this *has* to have something to do with it, I just can't figure out WHY. I've tested with dates in the past as well. Something on the Guernsey server seems to be the cause, but jezz - what? Is the web service doing anything with dateConvert()? Nothing at all. On the Guernsey side (simplified...): cfinvoke webservice=#request.cfcLOCATION#.. WSdepDATE=#form.depDATE# ... On the US side it's a simple insert statement with the date handled thusly: cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / That's it. Now, on the US side - it's also creating/recording the date the record is inserted using now(), and *THAT* date is correct. Go figure... Are you sure you're not supposed to be entering the date/times in GMT format? I'm doing a calculation between two dates on the form, and the calculation is coming out correct - the date just end up one day off. The only solution I can actually come up with was an earlier tongue-and-cheeck just post the dates a day in advance. Still, I'd like to know *why* it's happening exactly. I've never seen anything like this before. Coffee - must have more coffee input. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280716 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A Date Problem that's got me tearing out my hair!
I have worked with this all weekend, and can find no rhyme or reason at all for what's happening here. I enter into my forms: start date: 6/1/2007 end date: 6/10/2007 and what gets returned is: start date: 5/31/2007 end date: 6/09/2007 I'm completely stumped! What in the hell could be causing this? Just to recap - the website with form located in Guernsey. The forms calls a webservice that uses a CFC located on a HostMySite server here in the US and writes to a database here in the US. Are the HostMysite servers located in Deleware? There's a 5 hour difference between the server time in Guernsey and Deleware. It doesn't seem to matter what time of day I enter information into a form, the problem still occurs, so I'm not sure that's got anything to do with it. Anybody got any further ideas? ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280597 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
A Date Problem that's got me tearing out my hair!
I've got a site that's hosted in Guernsey (Island in English channel) with a form with date input fields. Pretty darned simple. input id=depDATE name=depDATE type=text value=#dateformat(now(),'mm/dd/')# / This form uses a web service to write to a MySQL database in the US. Also pretty simple: One the Guernsey side: cfinvoke webservice=#request.cfcLOCATION# method=ENROLLMENT_ONE returnvariable=getMAX WSdepDATE=#form.depDATE# On the US side: cffunction name=ENROLLMENT_ONE access=remote returntype=any hint=Add the initial enrollment record cfargument name=WSdepDATE type=date / And in my insert statement. cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / Looks fine, right? BUT - the dates stored are one date off from the dates entered. Somebody enters 7/21/2007 and 7/20/2007 is getting written to the database. What tha? Any ideas at all? There's a 6 hour difference between the servers in Guernsey and where the database lives in the US. Could that have anything to do with it? Ideas? Any at all? ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280521 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: A Date Problem that's got me tearing out my hair!
Because now() is server time... Very simple problem On 6/9/07, Les Mizzell [EMAIL PROTECTED] wrote: I've got a site that's hosted in Guernsey (Island in English channel) with a form with date input fields. Pretty darned simple. input id=depDATE name=depDATE type=text value=#dateformat(now(),'mm/dd/')# / This form uses a web service to write to a MySQL database in the US. Also pretty simple: One the Guernsey side: cfinvoke webservice=#request.cfcLOCATION# method=ENROLLMENT_ONE returnvariable=getMAX WSdepDATE=#form.depDATE# On the US side: cffunction name=ENROLLMENT_ONE access=remote returntype=any hint=Add the initial enrollment record cfargument name=WSdepDATE type=date / And in my insert statement. cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / Looks fine, right? BUT - the dates stored are one date off from the dates entered. Somebody enters 7/21/2007 and 7/20/2007 is getting written to the database. What tha? Any ideas at all? There's a 6 hour difference between the servers in Guernsey and where the database lives in the US. Could that have anything to do with it? Ideas? Any at all? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280524 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
Andrew Scott wrote: Because now() is server time... Not actually. The value DEFAULTS to now(), but anything can be entered and it still ends on storing as 1 day earlier even when a different date is entered. What I *haven't* tested yet is to see if there's a particular hour that this happens, but I'm playing with it now - doing a submission every hour and seeing what I get back. If it's something other than midnight and it's correct for a few hours, that'll give me a clue. Very simple problem On 6/9/07, Les Mizzell [EMAIL PROTECTED] wrote: I've got a site that's hosted in Guernsey (Island in English channel) with a form with date input fields. Pretty darned simple. input id=depDATE name=depDATE type=text value=#dateformat(now(),'mm/dd/')# / This form uses a web service to write to a MySQL database in the US. Also pretty simple: One the Guernsey side: cfinvoke webservice=#request.cfcLOCATION# method=ENROLLMENT_ONE returnvariable=getMAX WSdepDATE=#form.depDATE# On the US side: cffunction name=ENROLLMENT_ONE access=remote returntype=any hint=Add the initial enrollment record cfargument name=WSdepDATE type=date / And in my insert statement. cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / Looks fine, right? BUT - the dates stored are one date off from the dates entered. Somebody enters 7/21/2007 and 7/20/2007 is getting written to the database. What tha? Any ideas at all? There's a 6 hour difference between the servers in Guernsey and where the database lives in the US. Could that have anything to do with it? Ideas? Any at all? ~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280525 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
Well if you debug this you might just see that as I said is true. input id=depDATE name=depDATE type=text value=#dateformat(now(),'mm/dd/')# / If you always expect the default as mm/dd/yyy, and someone enters dd/mm/ you may end up with an invalid date, and your code might be trapping and returning a default now(). There could be many reasons, for what is happening. If they are entering the date, then there should not be a problem, unless the date is invalid how do you handle that. But what if they are leaving it as a default date? The server will report 6-9 hours difference depending on which part of the USA you are in. As I said there are many reasons, but it will be the localisation of the now() function running on the server in question that is the problem. As to what point that is happening is upto you to find. On 6/9/07, Les Mizzell [EMAIL PROTECTED] wrote: Andrew Scott wrote: Because now() is server time... Not actually. The value DEFAULTS to now(), but anything can be entered and it still ends on storing as 1 day earlier even when a different date is entered. What I *haven't* tested yet is to see if there's a particular hour that this happens, but I'm playing with it now - doing a submission every hour and seeing what I get back. If it's something other than midnight and it's correct for a few hours, that'll give me a clue. Very simple problem On 6/9/07, Les Mizzell [EMAIL PROTECTED] wrote: I've got a site that's hosted in Guernsey (Island in English channel) with a form with date input fields. Pretty darned simple. input id=depDATE name=depDATE type=text value=#dateformat(now(),'mm/dd/')# / This form uses a web service to write to a MySQL database in the US. Also pretty simple: One the Guernsey side: cfinvoke webservice=#request.cfcLOCATION# method=ENROLLMENT_ONE returnvariable=getMAX WSdepDATE=#form.depDATE# On the US side: cffunction name=ENROLLMENT_ONE access=remote returntype=any hint=Add the initial enrollment record cfargument name=WSdepDATE type=date / And in my insert statement. cfqueryparam cfsqltype=CF_SQL_DATE value=#WSdepDATE# / Looks fine, right? BUT - the dates stored are one date off from the dates entered. Somebody enters 7/21/2007 and 7/20/2007 is getting written to the database. What tha? Any ideas at all? There's a 6 hour difference between the servers in Guernsey and where the database lives in the US. Could that have anything to do with it? Ideas? Any at all? ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280526 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: A Date Problem that's got me tearing out my hair!
If you always expect the default as mm/dd/yyy, and someone enters dd/mm/ you may end up with an invalid date, and your code might be trapping and returning a default now(). There could be many reasons, for what is happening. I've got trapping that's being sure the date format is entered correctly. The form is calculating the number of travel days for a trip. So, if I enter 6/10/2007 as the departure date, and 6/19/2007 as the return date, what's ending up in the database is 6/09/2007 and 6/18/2007. The number of travel days is still being calculated correctly, it's just that the trip dates are off by one. I've also tried three drop-downs for month, day, year and then create a date from that. The above *still* happens. It's got to be something somewhere between the different time zones of the form itself, the CFC and the database on a different server in different time zones. Weird, huh? I'm hoping my once an hour or so test over the weekend will tell me something. ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280528 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Date Problem
I have an app that allows a user to enter certain dates about a candidate, and not all dates are required. So, what is happening is, if someone enters the Date Elected, and leaves the other two date fields blank, the date 1/1/1900 is entered by default. The form is a Flash Form and I am using the CFINPUT Type=DateField tag. They look like this: cfinput type=datefield name=DateElected label=Date Elected width=100 value=#DateFormat(DateElected, mm/dd/)# cfinput type=datefield name=AppointedDate label=Date Appointed width=100 value=#DateFormat(DateAppointed, mm/dd/)# cfinput type=datefield name=ResignDate label=Date Resigned width=100 value=#dateFormat(ResignDate, mm/dd/)# Neither of the three are required. So even if I do not enter a date into any of these fields, I get the default 1/1/1900 entered. Here is the code that enters the information: cfinvokeargument name=DateElected value=#Form.DateElected#/ cfinvokeargument name=AppointedDate value=#Form.AppointedDate#/ cfinvokeargument name=ResignDate value=#Form.ResignDate#/ And here is a snipped from the CFC: cfargument name=DateElected type=string required=no default= cfargument name=DateAppointed type=string required=no default= cfargument name=ResignDate type=string required=no default= and the query: UPDATE tblCouncilMember SET Fname = '#Arguments.Fname#', Lname = '#Arguments.Lname#', Address = '#Arguments.Address#', City = '#Arguments.City#', State = '#Arguments.State#', Zip = '#Arguments.Zip#', Phone = '#Arguments.Phone#', Email = '#Arguments.Email#', Status = '#Arguments.Status#', NC_ID = #Arguments.NC_ID#, IssuesOfConcern = '#Arguments.IssuesOfConcern#', DateElected = '#Arguments.DateElected#', DateAppointed = '#Arguments.AppointedDate#', ResignDate = '#Arguments.ResignDate#', TermLength = '#TermLength#' WHERE CouncilMemberID = #Arguments.CouncilMemberID# Thanks, -- Bruce Sorge I'm a mawg: half man, half dog. I'm my own best friend! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267486 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
you could use an imaginary date and cfparams on the page..
cfparam name=DateElected default=#dateformat('01/01/','mm/dd/')#/
but it looks like your dateformat() and passing null values to it is
causing the issue..
this way, you pass an imaginary date to the database..
unless you really want nulls, then you couldnt use the dateformat there..
On 1/24/07, Bruce Sorge [EMAIL PROTECTED] wrote:
I have an app that allows a user to enter certain dates about a candidate,
and not all dates are required. So, what is happening is, if someone enters
the Date Elected, and leaves the other two date fields blank, the date
1/1/1900 is entered by default. The form is a Flash Form and I am using the
CFINPUT Type=DateField tag. They look like this:
cfinput type=datefield name=DateElected label=Date Elected
width=100 value=#DateFormat(DateElected, mm/dd/)#
cfinput type=datefield name=AppointedDate label=Date Appointed
width=100 value=#DateFormat(DateAppointed, mm/dd/)#
cfinput type=datefield name=ResignDate label=Date Resigned
width=100 value=#dateFormat(ResignDate, mm/dd/)#
Neither of the three are required. So even if I do not enter a date into any
of these fields, I get the default 1/1/1900 entered. Here is the code that
enters the information:
cfinvokeargument name=DateElected value=#Form.DateElected#/
cfinvokeargument name=AppointedDate value=#Form.AppointedDate#/
cfinvokeargument name=ResignDate value=#Form.ResignDate#/
And here is a snipped from the CFC:
cfargument name=DateElected type=string required=no default=
cfargument name=DateAppointed type=string required=no default=
cfargument name=ResignDate type=string required=no default=
and the query:
UPDATE tblCouncilMember
SET Fname = '#Arguments.Fname#',
Lname = '#Arguments.Lname#',
Address = '#Arguments.Address#',
City = '#Arguments.City#',
State = '#Arguments.State#',
Zip = '#Arguments.Zip#',
Phone = '#Arguments.Phone#',
Email = '#Arguments.Email#',
Status = '#Arguments.Status#',
NC_ID = #Arguments.NC_ID#,
IssuesOfConcern = '#Arguments.IssuesOfConcern#',
DateElected = '#Arguments.DateElected#',
DateAppointed = '#Arguments.AppointedDate#',
ResignDate = '#Arguments.ResignDate#',
TermLength = '#TermLength#'
WHERE CouncilMemberID =
#Arguments.CouncilMemberID#
Thanks,
--
Bruce Sorge
I'm a mawg: half man, half dog. I'm my own best friend!
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267490
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
Bruce, I would break down the date. Allowing the user to enter 11/11/2007 is asking for a lot of validation. You probably should have a month, day and year input field for each date range. This makes it easier to validate. 1/1/1900 is a behavior of SQL Server smalldate field when you send it an empty string. If the field is NOT NULL, it will create a rudimentary date. In your update, you can have conditional logic using cfif to only update the field if you have a valid date when you try to create a date based upon the three input fields. Teddy On 1/24/07, Bruce Sorge [EMAIL PROTECTED] wrote: I have an app that allows a user to enter certain dates about a candidate, and not all dates are required. So, what is happening is, if someone enters the Date Elected, and leaves the other two date fields blank, the date 1/1/1900 is entered by default. The form is a Flash Form and I am using the CFINPUT Type=DateField tag. They look like this: cfinput type=datefield name=DateElected label=Date Elected width=100 value=#DateFormat(DateElected, mm/dd/)# cfinput type=datefield name=AppointedDate label=Date Appointed width=100 value=#DateFormat(DateAppointed, mm/dd/)# cfinput type=datefield name=ResignDate label=Date Resigned width=100 value=#dateFormat(ResignDate, mm/dd/)# Neither of the three are required. So even if I do not enter a date into any of these fields, I get the default 1/1/1900 entered. Here is the code that enters the information: cfinvokeargument name=DateElected value=#Form.DateElected#/ cfinvokeargument name=AppointedDate value=#Form.AppointedDate#/ cfinvokeargument name=ResignDate value=#Form.ResignDate#/ And here is a snipped from the CFC: cfargument name=DateElected type=string required=no default= cfargument name=DateAppointed type=string required=no default= cfargument name=ResignDate type=string required=no default= and the query: UPDATE tblCouncilMember SET Fname = '#Arguments.Fname#', Lname = '#Arguments.Lname#', Address = '#Arguments.Address#', City = '#Arguments.City#', State = '#Arguments.State#', Zip = '#Arguments.Zip#', Phone = '#Arguments.Phone#', Email = '#Arguments.Email#', Status = '#Arguments.Status#', NC_ID = #Arguments.NC_ID#, IssuesOfConcern = '#Arguments.IssuesOfConcern#', DateElected = '#Arguments.DateElected#', DateAppointed = '#Arguments.AppointedDate#', ResignDate = '#Arguments.ResignDate#', TermLength = '#TermLength#' WHERE CouncilMemberID = #Arguments.CouncilMemberID# Thanks, -- Bruce Sorge I'm a mawg: half man, half dog. I'm my own best friend! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267492 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Problem
That is what I want, nulls. If no date is selected, then I do not want
anything there.
Bruce Sorge
I'm a mawg: half man, half dog. I'm my own best friend!
On 1/24/07, Greg Morphis [EMAIL PROTECTED] wrote:
you could use an imaginary date and cfparams on the page..
cfparam name=DateElected
default=#dateformat('01/01/','mm/dd/')#/
but it looks like your dateformat() and passing null values to it is
causing the issue..
this way, you pass an imaginary date to the database..
unless you really want nulls, then you couldnt use the dateformat there.
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267495
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Date Problem
That is what I want, nulls. If no date is selected, then I do not want anything there. I would suggest using the CFQUERYPARAM tag and using its NULL attribute. If not that then you will be forced to do a CFIF myDate eq ''NULLcfelse#myDate#/cfif Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267497 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
I am not allowing the user to manually input the date. I am using the CFINPUT Type=DateField which creates a flash calendar, the user clicks on the date and voila! Date is entered. Problem is that if any of the others are not filled out, my database fields are getting populated with 1/1/1900 automatically rather than a null. Bruce On 1/24/07, Teddy Payne [EMAIL PROTECTED] wrote: Bruce, I would break down the date. Allowing the user to enter 11/11/2007 is asking for a lot of validation. You probably should have a month, day and year input field for each date range. This makes it easier to validate. 1/1/1900 is a behavior of SQL Server smalldate field when you send it an empty string. If the field is NOT NULL, it will create a rudimentary date. In your update, you can have conditional logic using cfif to only update the field if you have a valid date when you try to create a date based upon the three input fields. Teddy ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267498 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
Yeah, guess I am gonna have to use the CFIF option. SP's are not allowed on the DB2 server for the city. We are getting our own SQL Server and CF Server soon though, so I get to re-write the app using SP's. Bruce On 1/24/07, Richard Kroll [EMAIL PROTECTED] wrote: That is what I want, nulls. If no date is selected, then I do not want anything there. I would suggest using the CFQUERYPARAM tag and using its NULL attribute. If not that then you will be forced to do a CFIF myDate eq ''NULLcfelse#myDate#/cfif Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267499 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
yeah definitely use cfqueryparams when in your query.. cfqueryparam cfsqltype=cf_sql_date value=#form.foodate# null=yes / On 1/24/07, Richard Kroll [EMAIL PROTECTED] wrote: That is what I want, nulls. If no date is selected, then I do not want anything there. I would suggest using the CFQUERYPARAM tag and using its NULL attribute. If not that then you will be forced to do a CFIF myDate eq ''NULLcfelse#myDate#/cfif Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267502 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Date Problem
Based on the query you provided: UPDATE tblCouncilMember SET Fname = '#Arguments.Fname#', Lname = '#Arguments.Lname#', Address = '#Arguments.Address#', City = '#Arguments.City#', State = '#Arguments.State#', Zip = '#Arguments.Zip#', Phone = '#Arguments.Phone#', Email = '#Arguments.Email#', Status = '#Arguments.Status#', NC_ID = #Arguments.NC_ID#, IssuesOfConcern = '#Arguments.IssuesOfConcern#', DateElected = '#Arguments.DateElected#', DateAppointed = '#Arguments.AppointedDate#', ResignDate = '#Arguments.ResignDate#', TermLength = '#TermLength#' WHERE CouncilMemberID = #Arguments.CouncilMemberID# If for example you pass 'DateElected' but DateAppointed and ResignDate are not filled out the resulting query is passing an empty string to SQL, which as Teddy said will cause it to be stored as 1/1/1900. If you change all your date fields to one of the following it will solve your problem: 1. For example DateElected cfqueryparam cfsqltype=cf_sql_date value=#arguments.DateElected# null=#len(arguments.DateElected)# 2. cfif len(arguments.DateElected)#arguments.DateElected#cfelseNULL/cfif This will cause NULL to be passed to the database if the DateElected variable is an empty string. HTH, Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267503 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Date Problem
Yeah, guess I am gonna have to use the CFIF option. SP's are not allowed on the DB2 server for the city. We are getting our own SQL Server and CF Server soon though, so I get to re-write the app using SP's. As I understand it, using the CFQUERYPARAM tag is on the ColdFusion side and simply creates a prepared statement that is sent to the database and does not involve the use of stored procedures. Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267504 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Date Problem Resolved
OK, I in the Update query in the CFC, I just did this: cfif LEN(Arguments.DateElected) DateElected = '#Arguments.DateElected#' /cfif and so on. Worked like a champ. I was just hoping there was another way, or better yet, a good explanation why this was happening. Bruce ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267505 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Date Problem
OH. I guess I misunderstood the use of CFQUERYPARAM. I only use it on SP's. Bruce On 1/24/07, Richard Kroll [EMAIL PROTECTED] wrote: Yeah, guess I am gonna have to use the CFIF option. SP's are not allowed on the DB2 server for the city. We are getting our own SQL Server and CF Server soon though, so I get to re-write the app using SP's. As I understand it, using the CFQUERYPARAM tag is on the ColdFusion side and simply creates a prepared statement that is sent to the database and does not involve the use of stored procedures. Rich Kroll ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:267513 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Interesting Date Problem
As it is a birth date you know it must be in the past, you could therefore do a datediff() comparing the retuned date and the current date and if the returned date is in the future subtract 100 years with a dateadd(). -- James Smith - IT Director uWish Ltd - http://www.uWish.co.uk -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 04 January 2007 20:14 To: CF-Talk Subject: Interesting Date Problem Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265745 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Interesting Date Problem
Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265689 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Interesting Date Problem
Javascript or ColdFusion server side with a mask. Obviously ensuring that international dates are catered for - dd/mm/ This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Les Mizzell To: CF-Talk Sent: Thu Jan 04 20:13:42 2007 Subject: Interesting Date Problem Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265690 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Interesting Date Problem
gonna need more to go on, i think. using CFMX 7, the following code outputs 1945: cfoutput#createDate(45, 1, 22)#/cfoutput On 1/4/07, Les Mizzell [EMAIL PROTECTED] wrote: Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265691 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Interesting Date Problem
Les Mizzell wrote: Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 And if they enter 1/22/05, does that mean 1905 or 2005? I would force the 4 digit year...assumptions in data are bad. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265693 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Interesting Date Problem
Les, wht not break it up? Use three separate pulldowns (month, day year) and then combine the values before you write the data to the db. ~Che -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 3:14 PM To: CF-Talk Subject: Interesting Date Problem Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265694 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Interesting Date Problem
off-hand I'd say use DateFormat with a mask that included so that the year will be taken as a 4 digit year. But without seeing your code that is giving you trouble, I can't guarantee that this will work. It could be what you're already doing! :o) Post some code, that might help. Cheers, Chris Les Mizzell wrote: Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265695 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Interesting Date Problem
I have to agree with Jim here. Folks need to join the 21st century with the rest of the world. Get used to *always* entering the full four-digit year in a date field. Saving two keystrokes just doesn't make sense... and assumptions about data *are* bad. :o' Cheers, Chris Jim Wright wrote: Les Mizzell wrote: Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 And if they enter 1/22/05, does that mean 1905 or 2005? I would force the 4 digit year...assumptions in data are bad. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265696 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Interesting Date Problem
Is the person entering the date in mm/dd/yy format and the database is putting 2045 for the year, or are you storing it in the database in mm/dd/yy format and your output is 21st century? If the date is stored in the database in mm/dd/yy format, use the DateFormat function in the output and it should be fine. ColdFusion translates the years 00 - 29 as 21st century and 30-99 as 20th century dates so I do not think CF is doing it. Also, I would force the four digit year into the database as a precaution. Bruce -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 12:14 PM To: CF-Talk Subject: Interesting Date Problem Form field requesting a birth date. Person enters 1/22/45 Coldfusion returns 1/22/2045 How can I insure it returns 1/22/1945 without forcing the year entry as four digits- 1/22/1945 vs. 1/22/45 ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265697 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date problem between Coldfusion 5 and MX
Thanks ! it works great and it's much simpler than my artisanal trick :) On Thu, 27 Jan 2005 19:18:43 -0400, Larry White [EMAIL PROTECTED] wrote: Use This: cfset ModDate = lundi 17 janvier 2005 10 h 53 CET cfset x = SetLocale(French (Standard)) cfset faqdate= DateFormat(LsParseDAteTime(ModDate),dd/mm/) cfoutput#faqdate#/cfoutput Hi everyone, I'm upgrading a site from Coldfusion 5 to MX but I have a date bug. In Coldfusion 5, I got the last modified date of a file and converted it with DateFormat like this : cfset faqPath = GetTemplatepath() cfset faqPath = left(faqPath,len(faqPath)-9) wel\ cfdirectory action=list name=faqDate directory=#faqPath# filter=dspFaq.cfm cfset faqDate = left(faqDate.DATELASTMODIFIED,10) cfset faqDate = dateformat(faqDate,dd/mm/) My faqDate.DatelastModified with CF5 is like this : *01/17/2005 10:53:00 AM* But with MX, I get this : *lundi 17 janvier 2005 10 h 53 CET *So of course, the left() function can't be used with this string and it bugs. I used a artisanal trick to solve this problem, but I'd like to understand how MX deals with the system dates, which functions I could use... Thanks a lot, -- *Nath* http://spoutnik1307.free.fr/ ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192066 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Date problem between Coldfusion 5 and MX
Use This: cfset ModDate = lundi 17 janvier 2005 10 h 53 CET cfset x = SetLocale(French (Standard)) cfset faqdate= DateFormat(LsParseDAteTime(ModDate),dd/mm/) cfoutput#faqdate#/cfoutput Hi everyone, I'm upgrading a site from Coldfusion 5 to MX but I have a date bug. In Coldfusion 5, I got the last modified date of a file and converted it with DateFormat like this : cfset faqPath = GetTemplatepath() cfset faqPath = left(faqPath,len(faqPath)-9) wel\ cfdirectory action=list name=faqDate directory=#faqPath# filter=dspFaq.cfm cfset faqDate = left(faqDate.DATELASTMODIFIED,10) cfset faqDate = dateformat(faqDate,dd/mm/) My faqDate.DatelastModified with CF5 is like this : *01/17/2005 10:53:00 AM* But with MX, I get this : *lundi 17 janvier 2005 10 h 53 CET *So of course, the left() function can't be used with this string and it bugs. I used a artisanal trick to solve this problem, but I'd like to understand how MX deals with the system dates, which functions I could use... Thanks a lot, -- *Nath* http://spoutnik1307.free.fr/ ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192033 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Date problem between Coldfusion 5 and MX
Hi everyone, I'm upgrading a site from Coldfusion 5 to MX but I have a date bug. In Coldfusion 5, I got the last modified date of a file and converted it with DateFormat like this : cfset faqPath = GetTemplatepath() cfset faqPath = left(faqPath,len(faqPath)-9) wel\ cfdirectory action=list name=faqDate directory=#faqPath# filter=dspFaq.cfm cfset faqDate = left(faqDate.DATELASTMODIFIED,10) cfset faqDate = dateformat(faqDate,dd/mm/) My faqDate.DatelastModified with CF5 is like this : *01/17/2005 10:53:00 AM* But with MX, I get this : *lundi 17 janvier 2005 10 h 53 CET *So of course, the left() function can't be used with this string and it bugs. I used a artisanal trick to solve this problem, but I'd like to understand how MX deals with the system dates, which functions I could use... Thanks a lot, -- *Nath* http://spoutnik1307.free.fr/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191796 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Create date problem
I'm trying to convert this date/time info from the filed #datetime#: 20031201 03:38 PM into something CF can display correctly. I'm using CreateDate for 20031201. How do I also get the time 03:38 PM separated? In other words I would like to have the #DateFormat((Datetime), mmm-dd-)# display both the date and time formatted. This is what I have so far, but of course it gives me an error because of the 03:38 PM. CFSET Datetime = (CreateDate(left(datetime, 4), Mid(datetime, 5, 2), Right(datetime, 2))) #DateFormat((Datetime), mmm-dd-)# Thanks. Robert O. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create date problem
TimeFormat(YourDate, hh:mm:ss) in hh:mm:ss u can use more masks. regards __ MSc. Hassan Arteaga Rodrguez Microsoft Certified System Engineer. DIGI- Grupo de Desarrollo COPEXTEL, S.A. -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 08:32 AM To: CF-Talk Subject: Create date problem I'm trying to convert this date/time info from the filed #datetime#: 20031201 03:38 PM into something CF can display correctly. I'm using CreateDate for 20031201. How do I also get the time 03:38 PM separated? In other words I would like to have the #DateFormat((Datetime), mmm-dd-)# display both the date and time formatted. This is what I have so far, but of course it gives me an error because of the 03:38 PM. CFSET Datetime = (CreateDate(left(datetime, 4), Mid(datetime, 5, 2), Right(datetime, 2))) #DateFormat((Datetime), mmm-dd-)# Thanks. Robert O. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create date problem
Try this: cfset datetime = 20031201 03:38 PM cfset myDate = CreateDate( left(datetime, 4), Mid(datetime, 5, 2), Mid(datetime, 7, 2) ) cfset myTime = ParseDateTime( right(datetime,8) ) cfoutput #DateFormat( myDate, mm/dd/ )# #TimeFormat( myTime, hh:mm:ss tt )#br /cfoutput Take a look at the docs for ParseDateTime().It's a pretty useful function. http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt250.h tm#1110355 http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt250. htm#1110355 I'm using CFMX 6.1 and am not sure about the availability of this function in previous versions of CF. -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:32 AM To: CF-Talk Subject: Create date problem I'm trying to convert this date/time info from the filed #datetime#: 20031201 03:38 PM into something CF can display correctly. I'm using CreateDate for 20031201. How do I also get the time 03:38 PM separated? In other words I would like to have the #DateFormat((Datetime), mmm-dd-)# display both the date and time formatted. This is what I have so far, but of course it gives me an error because of the 03:38 PM. CFSET Datetime = (CreateDate(left(datetime, 4), Mid(datetime, 5, 2), Right(datetime, 2))) #DateFormat((Datetime), mmm-dd-)# Thanks. Robert O. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Create date problem
Robert, On 12/2/2003 at 09:32, you wrote: RO I'm trying to convert this date/time info from the filed RO #datetime#: 20031201 03:38 PM into something CF can display RO correctly. I'm using CreateDate for 20031201. How do I also get RO the time 03:38 PM separated? In other words I would like to have RO the #DateFormat((Datetime), mmm-dd-)# display both the date RO and time formatted. RO This is what I have so far, but of course it gives me an error RO because of the 03:38 PM. CFSET Datetime = RO (CreateDate(left(datetime, 4), Mid(datetime, 5, 2), RO Right(datetime, 2))) #DateFormat((Datetime), mmm-dd-)# cfscript // raw date/time string strdatatime=20031201 03:38 pm; // assumes 4-digit year, 2-digit month, and 2-digit day strdate=left(strdatatime,8); // assumes a space between date and time strtime=listrest(strdatatime, ); // make a valid date and format it thisdate=dateformat(createdate(left(strdate,4),mid(strdate,5,2),right(strdate,2)),mmm-dd-); // format time, if necessary thistime=timeformat(strtime,h:mm tt); writeoutput(thisdate thistime); /cfscript ~ Ubqtous ~ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Create date problem
Ryan, Worked like a charm. Thanks and thanks to the others for the input as well. Robert O. -Original Message- From: Hagan, Ryan Mr (Contractor ACI) [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:51 AM To: CF-Talk Subject: RE: Create date problem Try this: cfset datetime = 20031201 03:38 PM cfset myDate = CreateDate( left(datetime, 4), Mid(datetime, 5, 2), Mid(datetime, 7, 2) ) cfset myTime = ParseDateTime( right(datetime,8) ) cfoutput #DateFormat( myDate, mm/dd/ )# #TimeFormat( myTime, hh:mm:ss tt )#br /cfoutput Take a look at the docs for ParseDateTime().It's a pretty useful function. http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt250.h tm#1110355 http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt250. htm#1110355 I'm using CFMX 6.1 and am not sure about the availability of this function in previous versions of CF. -Original Message- From: Robert Orlini [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:32 AM To: CF-Talk Subject: Create date problem I'm trying to convert this date/time info from the filed #datetime#: 20031201 03:38 PM into something CF can display correctly. I'm using CreateDate for 20031201. How do I also get the time 03:38 PM separated? In other words I would like to have the #DateFormat((Datetime), mmm-dd-)# display both the date and time formatted. This is what I have so far, but of course it gives me an error because of the 03:38 PM. CFSET Datetime = (CreateDate(left(datetime, 4), Mid(datetime, 5, 2), Right(datetime, 2))) #DateFormat((Datetime), mmm-dd-)# Thanks. Robert O. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date problem with CFMX and Oracle
yes...use the Oracle to_date() around your dates. Also, try using cfqueryparam, it
can handle the date conversions automagically.
Doug
-Original Message-
From: Dave Carabetta [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 16, 2003 9:54 PM
To: CF-Talk
Subject: Re: Date problem with CFMX and Oracle
I am trying to do an update to my DB:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = {ts '2003-09-16 16:08:22'}
WHERE id = 2043
This worked fine before, I updated my CFMX server to 6.01 and my JDBC
drivers to 1.4, and am now getting column doesn't exist! Does anyone
know why this no longer works?
I understand that this worked before MX and now it doesn't,
but you're using
a completely different set of drivers (Oracle ODBC/Native
depending on your
previous CF version vs. JDBC), each with their own set of
quirks. As for the
exact reason you're having your problem, not sure, but I'd
chalk it up to
the way the JDBC drivers pass dates as opposed to the
ODBC/Native drivers.
I've been working with CF and Oracle since the early days of
CF and learned
a long time ago to use Oracle's TO_DATE() function around all
dates. Oracle
is very particular about date formatting, and this function is
the closest
to a silver bullet that I've been able to find. Also, it looks
like your
example is using the current date/time to insert. If that's
the case, then
use sysdate instead of passing in an ODBC-format date.
Regards,
Dave.
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
RE: Date problem with CFMX and Oracle
Ah, Oracle - you have to love it! More and more corporations it seems are
using this DBMS!
For good reason - it is very capable.
From a development point of view, especially with a frequently absent
database administrator in between, it can be a nightmare.
Why you are using this:
ChgDate = {ts '2003-09-16 16:08:22'}
Instead of a native ColdFusion function like CreateODBCDateTime(Now()) or
something is beyond me.
Your quotes could also be an issue, try double rather than single quotes.
Also the CFUPDATE function negates the need for the SQL - just ensure the
data going in is valid beforehand - eg:
cfupdate datasource=#Request.DataSourceName# tablename=ACTCFUG_News
formfields=NewsID,NewsTitle,NewsBody,NewsBodyTemp,NewsUpdated,NewsLocked,Ne
wsReleased,NewsApproved,NewsCompleted username=#Request.DataSourceUser#
password=#Request.DataSourcePassword#
The formfields are validated before going to the database - an error could
be trapped and handled by CFTRY or some other error handler.
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Re: Date problem with CFMX and Oracle
Ack! Don't use cfupdate as a solution to this problem. Dates in oracle
really aren't that hard. There's a number of ways you can do them. The
easiest solution for entering the current date/time (as someone mentioned)
is to let Oracle do it with sysdate. So, your query would be:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = sysdate
WHERE id = 2043
If you want to control the date/time, then do it like this:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = to_date('2003-09-16 16:08:22', '-mm-dd hh24:mi:ss')
WHERE id = 2043
Or, if you want CF to handle it automagically it would go like this:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = cfqueryparam cfsqltype=CF_SQL_TIMESTAMP value=2003-09-16
16:08:22
WHERE id = 2043
- Original Message -
From: Peter Tilbrook [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, September 17, 2003 9:29 AM
Subject: RE: Date problem with CFMX and Oracle
Ah, Oracle - you have to love it! More and more corporations it seems are
using this DBMS!
For good reason - it is very capable.
From a development point of view, especially with a frequently absent
database administrator in between, it can be a nightmare.
Why you are using this:
ChgDate = {ts '2003-09-16 16:08:22'}
Instead of a native ColdFusion function like CreateODBCDateTime(Now()) or
something is beyond me.
Your quotes could also be an issue, try double rather than single quotes.
Also the CFUPDATE function negates the need for the SQL - just ensure the
data going in is valid beforehand - eg:
cfupdate datasource=#Request.DataSourceName# tablename=ACTCFUG_News
formfields=NewsID,NewsTitle,NewsBody,NewsBodyTemp,NewsUpdated,NewsLocked,Ne
wsReleased,NewsApproved,NewsCompleted username=#Request.DataSourceUser#
password=#Request.DataSourcePassword#
The formfields are validated before going to the database - an error
could
be trapped and handled by CFTRY or some other error handler.
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Get the mailserver that powers this list at
http://www.coolfusion.com
Date problem with CFMX and Oracle
I am trying to do an update to my DB:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = {ts '2003-09-16 16:08:22'}
WHERE id = 2043
This worked fine before, I updated my CFMX server to 6.01 and my JDBC
drivers to 1.4, and am now getting column doesn't exist! Does anyone
know why this no longer works?
Ray Bujarski
Direct 858-845-7669
Pager 858-636-9900
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Get the mailserver that powers this list at
http://www.coolfusion.com
RE: Date problem with CFMX and Oracle
Mm..is Status a reserved word?
-Gel
-Original Message-
From: Ray Bujarski [mailto:[EMAIL PROTECTED]
I am trying to do an update to my DB:
UPDATE BookInventory
SET Status = 'Checked Out',
CheckedTo = 'rayb',
ChgDate = {ts '2003-09-16 16:08:22'}
WHERE id = 2043
This worked fine before, I updated my CFMX server to 6.01 and my JDBC
drivers to 1.4, and am now getting column doesn't exist! Does anyone
know why this no longer works?
~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
