Another Date Issue

Question

Post reply

Another Date Issue

Shark Energy

SSCrazy Eights

Points: 8389
More actions
April 17, 2008 at 7:17 am

#189442

Right situation is -
We have an app that runs a stored proc. This stored proc inserts a value into a table including a date of format "17/04/2008".
This worked fine up until the other day when the SQL and Web servers were rebooted following microsoft patches.
Now this fails, "Char into a smalldatetime value too big bla bla" error that you get.
Now I don't think it can be a SQL setting, as if I connect remotely from another sql server it runs this code fine in a query window and inserts the value in the "17/04/2008" format.
My question is...other than regional settings (they haven't changed) what could have caused this change in behaviour?
On a side note, what do all you DBAs use as your default date formats etc with sql?
Cheers

Viewing 14 posts - 1 through 13 (of 13 total)

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

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

I use either of these two: 'yyyymmdd hh:mm:ss.sss' or 'yyyy-mm-dd hh:mm:ss.sss'

The first should work regardless of regional settings.

😎

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

Well for years and years and years they have used ddmmyyyy so changing it now would mean a complete rewrite of thousands of stored procs.

I need to work out why something that worked now doesn't!

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 3

Now I've noticed even when we search this smalldatetime field using "17/04/2008" it returns "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

If I search for "04/17/2008" it returns correctly. This is crazy, there is nothing American Format about my server. Its all uk format.

The data, as usual, is stored as "2008-04-17 00:00:00".

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 4

Can anyone expand on this a little? It's most annoying. The developers aren't using yyyymmdd so I have to try and work out why the current environment is displaying this behaviour...

Regional Settings are ALL uk. Connected users are British English format.

On Server A we run this in a query window -

SELECT Convert(datetime, '31/10/2010')

This returns "The conversion of char data to datetime resulted in an out-of-range datetime etc"

Now we have a Server B with Server A registered. We run -

SELECT Convert(datetime, '31/10/2010')

This returns 2010-10-31 as expected.

So how the hell does that work?

Thanks evry much. Very urgent in that its drilling a hole in my head that all my knowledge threatens to spill out of in protest.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

System date format needs to be set to dmy server wide. I can't, for the life of me, remember how to set it server wide, but a trip through Books Online should help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Shark Energy (5/20/2008)
Can anyone expand on this a little? It's most annoying. The developers aren't using yyyymmdd so I have to try and work out why the current environment is displaying this behaviour...
Regional Settings are ALL uk. Connected users are British English format.
On Server A we run this in a query window -
SELECT Convert(datetime, '31/10/2010')
This returns "The conversion of char data to datetime resulted in an out-of-range datetime etc"
Now we have a Server B with Server A registered. We run -
SELECT Convert(datetime, '31/10/2010')
This returns 2010-10-31 as expected.
So how the hell does that work?
Thanks evry much. Very urgent in that its drilling a hole in my head that all my knowledge threatens to spill out of in protest.

I can't explain it, but would you try this on Server A and tell me if it worked:

SET DATEFORMAT DMY

GO

SELECT Convert(datetime, '31/10/2010')

GO

Thanks

😎

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

Do the following as well:

sp_configure 'show advanced options', 1

go

reconfigure

go

sp_configure

go

Check the value for Default Language. If the run value is 0, then it is set to us_english. You then need to do the following:

sp_configure 'default language', 23 -- this is for British

go

reconfigure

go

After doing this and before testing, you will need to disconnect and reconnect QA.

Let us know if this helps.

😎

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 8

Lynn...that worked! Didn't really know about setting the dateformat on the fly until recently. Makes sense that it would fix the issue. Does this then mean that the setting is in place full time on the other servers but not on this one?

The language is set to 23. Always do that on new server installs.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 9

Found it! I had changed the Default Language, but the SQL Service was still running as US English. Switched this, date works...sorted! SILLY!

Thanks all for your help.

davide73 Grasshopper Points: 14 More actions · Answer 10

Use ODBC Standard

For example, now it's 23:11:00, 21 May 2008 is:

{ts '2008-05-21 23:11:00'}

Let's a query:

CREATE TABLE mydata (

type varchar(30),

day datetime

)

INSERT INTO mydata VALUES (

'birthday',

{ts '2008-05-21 23:11:00'}

)

-Davide

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

Do you mean "ISO Standard"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 12

Interestingly I now have a server displaying alternative behaviour, and giving the same error when passed a date in the so called universal yyyymmdd format.

Everything is set for British UK. But for some reason a getdate() that returns as 2008-05-22 causes the out of range error. I'll post the answer IF I work it out.

I HATE DATES......until I solve this one 😉

EDIT: Ignore me. It is only returning the error if I was incorrectly typing 2008-05-22. The query just wasn't matching the getdate(), in which case returning empty. Ho Ho Ho. Pub lunch does that for you.

davide73 Grasshopper Points: 14 More actions · Answer 13

Taken from BOL 2000: "Writing International Transact-SQL Statements"

ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:

* { ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }

* { d 'yyyy-mm-dd'} such as: { d '1998-09-24' }

* { t 'hh:mm:ss'} such as: { t '10:02:20'}

-Davide