Comand to convert Datetime - diferences from sql 2000 to 2008 1

[renatoboscolo]

The following sql command works fine in my sql 2000 but not in my sql 2008:

select * from table1 where
fd_data >= convert(varchar(10),GETDATE(),101)


SQL 2000
- windows server 2003 portugues
- regional settings: English (United States)
- local: brasil
- non unicode: English (United States)



SQL 2008
- windows server 2008 portugues
- regional settings: English (United States)
- local: brasil
- non unicode: English (United States)

Error in SQL 2008:
"The conversion of a data type varchar to a datetime data type resulted in a value outside the range"

I can´t understand why doesn´t work since the regional setting are identical.

thanks for any help!

 

[Qik3Coder]

is fd_data a datetime?
You should normally be re-casting it back as a datetime for comparisions.

select cast(convert(varchar(10),GETDATE(),101) as datetime)

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[renatoboscolo]

Yes, it is a datetime field.

In fact, I know how to fix this command. But I am migrating from 2000 to 2008, and this means I would need to change all codes.

My question is why works in 2000 but not in 2008, since the regional settings are the same.

Is there a setting I change to the command still working?

thanks for your feedback!

 

[Qik3Coder]

I ran this on a 2k8 machine.
I have used that same snippet on 2k, 2k5, 2k8 boxes.

Normally I find issue when other data is entering the mix.
Are you sure the date is coming back as expected? Run the select and see what you get coming back.

Do you have the sql system setup for dd/mm/yyyy? I may be having issue comparing a string that it has to cast as opposed to a date it already knows about.

What is this "fix" that you know about, but don't want to implement?

Lod

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[gmmastros]

You didn't mention what the default language is for the login you are using. Specifically... date interpretation is based on the language of the login. The sample code you show converts a date to a string in the format "05/20/2011". Sitting here in America, I immediately recognize this as May 20. This is not the case for others around the world.

I'm reasonably confident that this is a "login language" issue. I explain it fully in one of my blogs:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should also mention that I wrote a free utility called SQLCop that can detect login language issues. You can install this app from the link in my signature. After logging in to the app, expand "Configuration" and then "Login Language".

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[renatoboscolo]

Thanks gmmastros!

It was a login language difference.

My login in SQL 2000 is in English but 2008 was in Brazilian. I changed to English and worked as it is in 2000 w/o change any code.

Thanks for all!

 

[gmmastros]

Please call me George.

I'm glad I was helpful to you. The reason your original code failed was caused by a login language problem, clearly there is no doubt about that. However, I would like to make another suggestion.

select * from table1 where 
fd_data >= convert(varchar(10),GETDATE(),101)

By converting GetDate() to a varchar(10), you are effectively removing the time component. Clearly the intent of this code is to return all the rows from the table where the fd_data column has a date of today, but any time today. There is a better way to write this, a method that does not suffer from login language dependence and is also faster to execute.

Select * 
from   table1
Where  fd_data >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

I realize this is more code than the original version of the query, but it is also better code. Basically, this code will calculate the number of days that have expired between 0 (Jan 1, 1900) and Now (GetDate()). This returns an integer. Then you add that whole number of days back to 0 (Jan 1, 1900).

In this version of the query, you start with a datetime, convert to an integer, and then back to a datetime. In your original, you started with a date, converted to a string, and then SQL Server automatically converted it back to a date for comparison purposes. Since there was a conversion to string and back, there was also room for ambiguity between language settings. By converting to integer and back to datetime, there is no ambiguity so it always works. Also, converting to int and back to datetime is faster than converting to string and then back to date time.

I realize it may not be feasible (right now) to modify all of your code because there may be too much to do, and it would take too long. Instead, I would encourage you to start using this method for new queries and any time you need to modify an existing query (that uses your old method) you change it to the new method.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom