datetime 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)

datetime problem

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-16 : 11:22:53

Hi,

We are using an application and when we run server profiler we see the following insert statement:
INSERT INTO SearchTable([SearchText],[SEARCHTYPE],[MACID],[SESSIONID],[CLIENTID],[PORTALID],
[USERID],[SEARCHFILTERS],[PROXIMITYSEARCHTERM],[CREATEDATE],[MODIFIEDDATE], [CASESENSITIVE],[WILDCARD]) VALUES (N'wikipedia', 1, N'00192107367B',
N'sbd0pcrwsqzj5', 12, 1, 0, N'SEARCHEAREA*FULLTEXT', N'NULL', '16/02/2009 15:21:59', '16/02/2009 15:21:59', 0, 0)

However this results in the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If we manually insert the exact same values into the table then the row is accepted without problems.

If i change the date to 02/16/2009 15:21:59 then the insert query succeeds and in the database i see the date as '16/02/2009 15:21:59'

The default language currently is USA. We tried changing it also to UK and no success either.

Can someone please point me in the right direction?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 11:28:29

Any of these will fix your problem

1) Use SET DATEFORMAT DMY
2) Use ISO datetime format yyyymmdd

E 12°55'05.63"
N 56°04'39.26"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-16 : 11:56:33

The problem is that it's not our application. We don't have access to the code. Therefore, we need to change something in our database.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 12:16:52

quote:
Originally posted by collie

The problem is that it's not our application. We don't have access to the code. Therefore, we need to change something in our database.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

you can add the set dateformat code to iunsert procedure to avoid this problem

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-16 : 12:20:15

Hi,

This isn't a stored procudure but a normal query that I can see in the profiler

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 12:21:17

quote:
Originally posted by collie

Hi,

This isn't a stored procudure but a normal query that I can see in the profiler

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

add the statement like this:-


SET DATEFORMAT dmy
....
your actual code

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-16 : 12:30:54

Thanks for the replies.

I went to query analyser and wrote
'SET DATEFORMAT dmy'

When I run DBCC UserOptions I ge the following but still the insert statement gives me the datetime error:

textsize 2147483647
language us_english
dateformat dmy
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

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources