Dateformat problem in Query Analyser

Hi Everybody,
i have a small problem ?
i have a two different servers
one is used for test purposes and the other one is used for live proposes

i have a table call Employee in both the servers and i got a filed call Attnd_Dttm

so when i open a query analyzer from the test database and type
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
i am getting the correct results

but when i am type the same SQL from the live databse
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
it give me the
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

My problem is how come this happen ,cos i checked form the live database server computer it's using a British standard date time format like dd/mm/yyyyy ?

Any Idea to solve this problem,cos i need to run the same SQL in both the servers without any problems ?

regards
suis

Try using this date format: 2005-01-13. (year, month, day, dash-separated). This format seems to work in all localizations.

-Ryan / Kardax

|||Hi Ryan
u r reply is not clear to me,
where can i give this command !

is there any command to find out the sql server date time format
regards
suis
|||

I think that you have differenet default DATEFORMAT option on your servers.

You could use SET SET DATEFORMAT 'mdy'

Or use explicit convert:

select * from Employee where convert(datetime,Attnd_Dttm,103) like '13/01/2005'

|||HI Konstantin Kosinsky
Thanks very much for your comments
u r solution is worked out,
but is there any command to check SQL server DateTime fornat ?

regards
suis

|||

suis,

I wonder if you could alter that column a make it datetime data type. If you can not change the data type of that column, I will suggest to store the value using ISO ('yyyymmdd') or ISO8601 ('yyyy-mm-ddThh:mis.mmm'). This way, SQL Server can interpret the string as a datetime no matter the language or formatdate settings.

Code Snippet

set dateformat dmy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set dateformat mdy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language Spanish

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language English

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

AMB