Query based on Regiional Setting

Question

Query based on Regiional Setting

Syed Anis

Say Hey Kid

Points: 685
More actions
October 10, 2006 at 1:35 pm

#367643

Below is my query
"SELECT * from SalesOrder WHERE
((docdate >= CONVERT(smalldatetime,@DocDateFrom)) AND (docdate <= CONVERT(smalldatetime,@DocDateTo) )) "
@DocDateFrom = 09/01/2006 & @DocDateTo = 09/30/2006
Its working fine when I have regional Setting US (English) but when I changed my regional setting to English (UK) its giving me error messages as
"Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
How I can write a query that it worked for all regions. Please help!!!
Syed

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It is the format of the dates in @DocDateFrom and @DocDateTo. These are in the US format. When you change to UK, the date format changes from mm/dd/yyyy to dd/mm/yyyy. Try changing the the dates in your variables to yyyy-mm-dd format and try changing your regional settings again.

hth,

Syed Anis Say Hey Kid Points: 685 More actions · Answer 2

Thanks Lynn for your reply. Yep, that is something happening because of the dd/mm/yyyy in UK. I tried earlier to change the variable to yyyy-mm-dd but I couldn’t do it. There is no FORMAT function in SQL Server. I was using CAST but couldn’t find a way to convert a variable to yyyy-mm-dd. could you please help me on this one too.

Thanks a bunch

Syed

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

If you have hardcoded the dates, just change in the set statement. I guess it depends on how and where the dates are coming from for you queries. If it is coming from an application at the user end, you need to have the application format the date in yyyy-mm-dd format for you before it sends it to your database (sproc, dynamic sql, et al). If the dates come from the database itself, you shouldn't have a problem with the date format.

vadba SSChampion Points: 11133 More actions · Answer 4

You have two options:

1. Try adding this line of code before converting to smalldatetime:

SET DATEFORMAT DMY

2. the YYYYMMDD format works with all regional settings. It is the best format to use with date constants. If the dates are parameters, and you can't change the format, you can convert them as shown below. I prefer YYYYMMDD without dashes, but that's just personal preference.

The code depends on zero-padded months and days (i.e. 09/01 versus 9/1).

DECLARE @DocDateFrom varchar(10)

, @DocDateTo varchar(10)

SET @DocDateFrom = '09/01/2006'

SET @DocDateTo = '09/30/2006'

SET @DocDateFrom = Right(@DocDateFrom,4) + Left(@DocDateFrom,2) + Substring(@DocDateFrom,4,2)

SET @DocDateTo = Right(@DocDateTo,4) + Left(@DocDateTo,2) + Substring(@DocDateTo,4,2)

PRINT @DocDateFrom

PRINT @DocDateTo

vadba SSChampion Points: 11133 More actions · Answer 5

Just to be clear regarding SET DATEFORMAT:

If the dates are received as '09/30/2006' then use SET DATEFORMAT MDY

If the dates are received as '30/09/2006' then use SET DATEFORMAT DMY

Syed Anis Say Hey Kid Points: 685 More actions · Answer 6

Thanks guys for all your help. Your help is greatly appreciated!!!

Regards,

Syed