SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Wed, 26 Jul 2006 23:09:23


Hi,

trying to use a simple statement "select * from example where fromdate <=
{fn curdate()}" and getting error 242.
The column "fromdate" in table "example" has the "datetime" datatype.
so why the conversion is not possible?

greetings

Michael Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by stchen » Thu, 27 Jul 2006 13:22:41

Hello Michael,

Welcome to the SQL Server MSDN newsgroup.

From your description, I understand you're getting some convertion error
when using the CURDATE function in your T-SQL select statement(for SQL
Server 2005).

Based on my understanding, the CURDATE function is an undocumented internal
function which will return the current Datetime's Date part. Since I
haven't found any definite document on its type, I suggest you try
explicitly converting it before use it in your select statement. You can
consider the following options:

1. We can use a temp variable (declare as dateime) to store the CurDate()
return value first and reference that variable in our select statement.

2. We can explicitly convert the CurDate()'s return value to Datetime when
directly embed it into our select statement. e.g

select * from xxxxx where xxxxDate < CONVERT(DATETIME, {fn CURDATE()})

In addition, do you think it's possible that we use the "GetDate" function
to replace the "CurDate" in your scenario? "GetDate" is a documented
function which will be much recommended in our application's T-SQL code.
If you do want to get the Date part only, we can also use T-SQL to convert
the GetDate() result to the date only value. e.g.

CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)

Hope this helps some. If you stll have any other questions on this, please
feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to

http://www.yqcomputer.com/ #notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial

response from the community or a Microsoft Support Engineer within 1
business day is

acceptable. Please note that each follow up response may take approximately
2 business days

as the support professional working with you may need further investigation
to reach the

most efficient resolution. The offering is not appropriate for situations
that require

urgent, real-time or phone-based interactions or complex project analysis
and dump analysis

issues. Issues of this nature are best handled working with a dedicated
Microsoft Support

Engineer by contacting Microsoft Customer Support Services (CSS) at

http://www.yqcomputer.com/

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Thu, 27 Jul 2006 17:17:30

Hello Steven,

thanks for the quick reply.

The "{fn CURDATE()}" function is a (documented) ODBC Scalar Funcion decribed
in "ODBC Programmer's Reference"
( http://www.yqcomputer.com/ )
. And SQLGetInfo called on "SQL Native Client" with informationtype
"SQL_TIMEDATE_FUNCTIONS" returns that the use of this function is supported.

I'm using the scalar funtion because the statement schould work against
different Databases (Sybase ASA, IBM DB2, Oracle an MS SQL). The "GETDATE()"
function is native to SQL Server and (whats more a problem) it returns date
and time information, but I only need the datepart.

"{fn CURDATE()}" schould return something like this '2006-07-26
00:00:00.000'. But even if I try to explicitly convert this with "{fn
CONVERT({fn CURDATE()}, SQL_TIMESTAMP)}" I get the 242 errror. In our
scenario we can do nothing thats native to SQL Server, we always need
SQL-syntax that is compatible with other databases.


kind regards


Micahel Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Thu, 27 Jul 2006 19:57:54

Hello Steven,

I tried this "{fn CONVERT({fn REPLACE({fn CURDATE()}, '-', '')},
SQL_TIMESTAMP)}" and it works correctly. But don't you think it's a little
bit difficult to do so, just to get a datetime datatype out of the "{fn
CURDATE()}". It's even no solution for me, because other Databases return a
datetime datatype when "{fn CURDATE()}" is called so the example above does
not work on this databases.

It would be really much easier if the function "{fn CURDATE()}" would return
a datetime datatyp by itself instead of a char datatype. I think thats what
a normal developer would expect when calling a "date time" function.

kind regards

Michael Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by stchen » Fri, 28 Jul 2006 22:03:13

Thanks for your reply and the further description,

I've got that you're trying to programing your data access application
against the ODBC specification so that it'll be more flexible to change
between different DBMSs. Yes, I can fully understand your concern here that
it is quite important to make the statement simple and consistent with
other DBMSs. Regarding on the CURDATE's implementation in SQL2005, I would
like to perform some further research, would you please send me a simple
test project to demonstrate the problem? I would refer to it if I would
need to leverage some further resource on this issue.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Tue, 01 Aug 2006 21:51:46

Hello Steven,

for testing we use the "Query Tool (using ODBC)" from George Poulose
( http://www.yqcomputer.com/ ). You can use SQL Statements witch includes
scalar functions with this tool. The results are quiet the same as if the
statements would have been launched from our application.

To demonstrate the problem connect with the "Query Tool" to a SQL2005
Database and run a statement like this "select {fn convert({fn curdate()},
sql_timestamp)} from msdb.dbo.syssessions". You'll get a "242" error. If you
change the statement like this "select {fn convert({fn replace({fn
curdate()}, '-', '')}, sql_timestamp)} from msdb.dbo.syssessions" it will
succeed.

hope this helps

sincerely,

Michael Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by stchen » Wed, 02 Aug 2006 20:37:28

Thanks for your reply Michael,

I've just downloaded the tool(Query Tool 5.0 using ODBC) you mentioned and
perform some tests against some ODBC datasources (connecting to SQL Server
2005 database instances) in my local environment, however, the T-SQL which
use {fn CURDATE()} can be executed correctly in that tool. My tested is
very simple and based on the following T-SQL script:

#using the Adventureworks sample database
======================

declare @date datetime

set @date = {fn CURDATE()}

select @date

select WorkOrderID, StartDate from Production.WorkOrder where
Production.WorkOrder.StartDate < {fn CURDATE()}

======================

Also, I have tested the same script against two system ODBC datasources.
One is created through the old SQL Server 2000 odbc driver, another is
using the new SQL Native Client ODBC driver(specific to SQL Server 2005).
The query work in both cases. Therefore, I'm wondering whether this is a
script specific or environment specific issue? My SQL Server 2005 is
installed on a windows 2003 server machine (with SP1), and the SQL Server
2005 itself is also applied SP1.

Is your local test also based on the Query Tool(using ODBC) or is there
anything I missed in the test script? Anyway, I'll try testing through
some custom code (using .net managed ODBC driver or C++ ODBC api) to see
the behavior, I'll also update you later. Meanwhile, if you have any
further finding, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Wed, 02 Aug 2006 23:28:45

Thanks for your testing Steven,

well you see me very confused now. I couldn't believe your testresults, so I
downloaded the Version 5.0 of the QueryTool (because I testet with Version
4.1 before).

What schould I say, it works on my System too.
Okay, I switched back to Version 4.1, it works too.
I tried an other Workstation with Version 4.1, it works too.
Can't believe, so I tried it from within our data access application, it
works too.

All the days past, the same statement resulted in error "242". So what has
changed???

I'am still searching for an answer and I think you'r rigth, it must be an
environment specific issue.

I'll post here if I find the reason and I apollogize for wasting your time,


kind regards

Michael Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Wed, 02 Aug 2006 23:57:08

lucky that I'm not crazy;-)

the problem is, that you tried it today on August 1st 2006.

When you run the statement "SELECT {fn CONVERT({fn CURDATE()},
SQL_TIMESTAMP)} FROM MSDB.DBO.SYSSESSIONS" on August 1st 2006 you'll see
that the date is convertet to "2006-01-08 00:00:00.000" witch is the January
8th 2006.

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 Brmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Thu, 03 Aug 2006 00:16:26

at least, the problem is the implementation of the conversion function!

When converting a date-string the conversion function actually expects it in
the format "yyyy-dd-mm" (year, month, day),
but a sql date string generally must allways have the format "yyyy-mm-dd"!!!

the conversion function must be corrected, I think...


Sincerely,

Micahel Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by Michael Br » Fri, 04 Aug 2006 21:50:18

Thanks for the quick reply,

the result of the conversion of a sql datestring (in (so called) ISO-Format)
schould never depend on the locale settings of the system it's executet on.

The conversion function of SQL 2005 must ever expect the string being in
ISO-Format (ISO 8601) so it must be (absolute independet from any locale
settings) always in the format 'YYYY-MM-DD'.

I think, it absolutly can't be a solution to change the system settings of a
german OS to display american dateformats.


Sincerely,

Michael Bruegmann
Nord-Soft GmbH
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by stchen » Sat, 05 Aug 2006 15:33:29

Thanks for your reply Michael,

Yes, I agree that the Date converting is not rely on the regional setting,
I mentioned it because those query tools such as the Query Analyzer or
management studio will display the final date/time result according to the
current logon user's regional setting. For SQL Server internal date/time
converting or persisting, it surely use its internal format which is
culture/region neutral. Currently the difficulty here is that the problem
is still hard to reproduct definitely on a generic machine, would you send
me a test database which will result the error when performing query (use
CurDate function) against it? If so, I can perform some further test
against it.

BTW, is this a common issue among all other SQL Server instances in your
environment (on other machines with different databases)?

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.
 
 
 

SQL Server 2005: Cannot convert {fn CURDATE()} to "datetime" datatype

Post by stchen » Tue, 08 Aug 2006 21:28:21

Hi Michael,

Thanks for your reply.

I've viewed the trace log you attached and did see the error entry about
the following statement:

"SELECT {fn CONVERT({d '2006-07-31'}, SQL_TIMESTAMP)}\ 0"

However, the difficulty here is that we're limited to the test environment
on our side. Since the issue require a localized SQL Server installed on a
localized OS machine, due to the limitation of the newsgroup interface, I
would suggest you consider contacting the local CSS for further assistance.
You can provide a complete environment info and reproduce steps list so
that the CSS engineer can help perform thorough test and troubleshooting.

You can find your local CSS support center's information in the following
site:

http://www.yqcomputer.com/

It's a pity that we haven't been able to help you further and thanks for
your understanding.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.