Date Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date Problem

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-04 : 10:14:15

Gurus,

If I exec query in analyzer
SELECT * FROM customerOrder WHERE date > '01/OCT/2007'
it is given me error
-- Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

but if i exec
SELECT * FROM customerOrder WHERE date > '01/OKT/2007'

Then its fine.
can ne one help me what is the problem is.

Thanks
Krishna

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-04 : 10:19:57

what language is it set to? sounds like maybe it's set to german

Em

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-04 : 10:22:06

Its USA english Collation is COLLATE SQL_Latin1_General_CP1_CS_AS

But Date is not depend with collation. Am i right..?

Thanks
-- Krishna

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-04 : 10:26:34

Run this:

EXEC dbcc useroptions

and see which language SQL server is installed in.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-04 : 10:28:39

textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 10:40:16

You should always express date values in YYYYMMDD format

Try

SELECT * FROM customerOrder WHERE date > '20071001'

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-04 : 11:00:24

Thanks Madhi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 02:03:29

quote:
Originally posted by CSK

Thanks Madhi

Did it work?

Madhivanan

Failing to plan is Planning to fail

gerten
Starting Member

17 Posts

Posted - 2007-10-05 : 02:49:11

Hi madhivanan!

Tryed to test the 'EXEC dbcc useroptions' as you write but gett error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'dbcc'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 04:14:00

quote:
Originally posted by gerten

Hi madhivanan!

Tryed to test the 'EXEC dbcc useroptions' as you write but gett error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'dbcc'.

That should be

dbcc useroptions

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources