Curdate() ODBC Error

Question

Curdate() ODBC Error

David-420660

Mr or Mrs. 500

Points: 554
More actions
December 15, 2008 at 7:43 pm

#126537

Hi.
I have an issue with my PeopleSoft team. Two months ago (Sept 30) they told me about some problems with the collation or language of the database because the {fn curdate()} didn´t work with a datetime column, so I changed the collation to an SQL_Latin* they asked; happily It worked the next day (Oct 1).
Today they came again and told me that it is not working again. So I googled that issue with no luck. The only thing I've found is that curdate is in "YYYY-MM-DD" format, so I believe the change I made 2 months ago actually didn't work. What really made the difference was that the day after the change the overflow was solved because of the change in date formats from 2008-09-30 to 2008-10-01.
Could that be the reason it worked 2 months ago and now it is not working?
Can I do anything to solve that issue?
I've executed this query in SQL 2008 and worked like a charm but not in 2005:
SELECT GETDATE() as A, {fn CURDATE()} as B
into #1
Select * FROM #1
Select * FROM #1 where A>{fn CURDATE()}
drop table #1
I think is because of something new in 2008.
•http://technet.microsoft.com/es-es/library/bb510680.aspx
•http://msdn.microsoft.com/es-es/library/ms714639(en-us,VS.85).aspx
•http://www.mydatabasesupport.com/forums/sqlserver-odbc/186986-sql-server-2005-cannot-convert-fn-curdate-datetime-datatype-2.html
Thanks and Best Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hello,

you didn't specify the error you are getting when it doesn't work... however, I suppose this will be a problem caused by language/dateformat setting (default date format is set within language settings, but you can override it with SET DATEFORMAT).

Try this:

SET DATEFORMAT ymd

-- same result will be with setting MDY

SELECT GETDATE() as A, {fn CURDATE()} as B

into #1

Select * FROM #1

Select * FROM #1 where A>{fn CURDATE()}

drop table #1

and this:

SET DATEFORMAT ydm

SELECT GETDATE() as A, {fn CURDATE()} as B

into #1

Select * FROM #1

Select * FROM #1 where A>{fn CURDATE()}

drop table #1

So I guess that date format in your database is YDM. Be very careful when considering to change settings like that - some other parts of the code may already rely on the current setting.

In fact, any code should be written so that it is independent on settings - unfortunately I have no idea what fn CURDATE() is, why are you using that and not GETDATE() or CURRENT_TIMESTAMP?

Generally, if you perform any conversion of character to datetime data, you should use CONVERT and specify precisely what is the format of the string using style.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi David

Your question is answered here:

Because the ODBC-Driver makes "{fn CURDATE()}" beeing a char datatype the
conversion of "2006-08-01" to number results in an expression witch can be
convertet to "2006-01-08" as a datetime datatype. Seems this isn't possible
with all dattime strings.
If you change the Systemdate of the SQL Server back to "2006-07-28" (for
example) the statement results in error "242".
So in fact it's a problem with the implementation of the "{fn CURDATE()}"
function in the ODBC driver.
Sincerely,
Michael Brügmann

{fn CURDATE()} is the driver-specific function for returning the current date as a string - is there a good reason why you are using this in preference to GETDATE(), which returns date as datetime?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Aah.. so CURDATE returns string... that's what I suspected. Fine, can you replace the CURDATE function with any of the mentioned (GETDATE(), CURRENT_TIMESTAMP) converted to character data?

Example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 110)

The number 110 denotes conversion style, and different values allow you to create strings in various ordering of day, month and year, with or without century, with ., - or / as separator etc. Refer to SQL Books online for more on CONVERT function.

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

From my point of view, this might mean that your function always presented wrong data: the day and month are switched. Perhaps you should inform your team about this... If you have any doubt, you might investigate if this date problem occurs anywhere else in your database.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

sample1 Ten Centuries Points: 1197 More actions · Answer 5

this query might solve your issue

SELECT GETDATE() as A, convert(datetime,{fn CURDATE()}) as B

into #1

Select * FROM #1

Select * FROM #1 where A>{fn CURDATE()}

drop table #1

David-420660 Mr or Mrs. 500 Points: 554 More actions · Answer 6

Thanks.

As I thought, this is a datetime format issue. My PeopleSoft team says that {fn CURDATE()} is the only way they can perform querys, so they are asking for a permanent change in database settings. Personally, I prefer use GetDate() function.

Casting the CURDATE function is not possible because, as I understood, the PeopleSoft team uses a IDE with some predefined functions.

Is there any way to change DateTime format settings in the Database?

Best Regards

jgrubb SSCrazy Points: 2542 More actions · Answer 7

David (12/16/2008)
Thanks.
As I thought, this is a datetime format issue. My PeopleSoft team says that {fn CURDATE()} is the only way they can perform querys, so they are asking for a permanent change in database settings. Personally, I prefer use GetDate() function.
Casting the CURDATE function is not possible because, as I understood, the PeopleSoft team uses a IDE with some predefined functions.
Is there any way to change DateTime format settings in the Database?
Best Regards

I'm still unclear on something.

Are they using ODBC, and passing in {fn CURDATE()}? Because the driver is not necessarily passing that to the DB. for any of {fn} tokens in a query, the driver should be translating them to SQL Dialect specific terms, which MAY NOT BE {fn CURDATE()}. SQL Server allows in a query string most of the ODBC functions, but that doesn't guarantee that's what is hitting the server. Check a profiler trace for the actual query.

If they are using ODBC, the programmers are doing it correctly for portability. The driver is supposed any translations and formatting. The problem is likely that they are getting the value comparing it to a string or some other faux-pas.

David-420660 Mr or Mrs. 500 Points: 554 More actions · Answer 8

SSC

After some research, I've found the issue. PeopleSoft automatically creates some views with queries with Database Manager System compatibility (those are supposed to run in any DBMS like SQL, Oracle, etc). So, the view with the issue is created with the {fn CURDATE()} syntax by default.

Changing the views is not an option because those changes would be lost in any maintenance operation like restore or migration. So, I've tried the options this forum gave me and I found that changing default language is the solution. However it's still not working.

Thanks a lot and I'll let you know how I made it work.

Regards 🙂