Date Question - 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 2000 Forums

Transact-SQL (2000)

Date Question

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2005-12-31 : 10:14:19

Hello,

I am sending 30/12/2005 throguh parameters from ASP.NET to SQL 2000 and it is saying that it is out of range when I call an SP that has a BETWEEN @FromDate AND @ToDate datatime parameters.. The SQL server is running on the my machine. The date format is English UK and should take dd/MM/yyyy fine. What is wrong? Even when I select through Query Analyzer dae columns return as 2005-12-30 00:00:00.000

I need to query for date in dd/MM/yyyy format.

Thanks for help

cfederl
Starting Member

26 Posts

Posted - 2005-12-31 : 11:48:46

"Even when I select through Query Analyzer date columns return as 2005-12-30 00:00:00.000"
Under Query Analyzer, dates will always be displayed in ISO format of YYYY-MM-DD HH:MM:SS:ms.

The dateformat setting is used only to convert character strings TO date values. It has no effect on the display of date values.

To see the value for dateformat, under Query Analyzer, run "dbcc useroptions".

I recommend never using dateformat or any operating system regional setting for dates but instead always use the ISO formats of:

{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '}
{ d 'yyyy-mm-dd'}
{ t 'hh:mm:ss'}

ASP.NET should therefore pass the string { d '2005-12-31'}

The output of "dbcc useroptions" will look like:

Set Option Value
--------------- ---------------
textsize 64512
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET

Carl Federl

BigRetina
Posting Yak Master

144 Posts

Posted - 2005-12-31 : 15:20:42

Thanks for the reply but...forget about ASP.NET, When I write the following statement in query analyzer i am getting an out of range error

SELECT * FROM Load
WHERE ImportDate BETWEEN '30/12/2005 00:00:00' AND '31/12/2005 23:00:00'

It only works when I write the date in MM/dd/yyyy....

What should I change to make the the analyzer/database accept the dd/MM/yyyy format?

Thanks for the help.

cfederl
Starting Member

26 Posts

Posted - 2005-12-31 : 18:08:39

There are a couple of solutions:
1. Set the dateformat to specify that date strings are in dmy
2. Set the login language to British

If you leave the language as us_english, then on every connection you would need to override the dateformat, which is subject to errors. Hence, I would recommend setting the default language for the login used by the ASP.Net application to British as there are other defaults that may be needed.

To set the languge using Query Analyzer, run
exec sp_defaultlanguage @loginame = 'login' , @language = 'British'

Here is some SQL that allows you to see the affects of the various options:

set dateformat mdy
select cast ( '06/12/2005' as datetime )
go
set dateformat dmy
select cast ( '06/12/2005' as datetime )
go
SET LANGUAGE us_english
select cast ( '06/12/2005' as datetime )
go
SET LANGUAGE British
select cast ( '06/12/2005' as datetime )
go
sp_helplanguage
go
sp_helplanguage @language = 'British'
go

Carl Federl

Kristen
Test

22859 Posts

Posted - 2006-01-01 : 06:36:28

I'd add:

3. Specify your dates in either

"yyyymmdd hh:mm:ss.mmm" or "yyyy-mm-ddThh:mm:ss.mmm" format

Note that there are NO hyphens in the first and there ARE hyphens in the second. You will get away with hyphens in the first format on many, but not all!, implementations of SQL Server.

Then it doesn't matter how your server is set up - which can be important if your database gets moved to another server, in the future, which is set up differently (and, say, can't be changed because it has other databases/applications on it)

If your "string date" is arriving from some other source, over which you have no control, then use SET DATEFORMAT dmy to indicate the sequence of the constituent parts.

If you are just "mucking about with Query Analyser", say, then use the month name if you can't easily work with yyyymmdd format [for example, I'm dyslexic and I find yyyymmdd hard to read & interpret] then use the month name - its unambiguous [but it IS dependant on the Language - but English works for USA and UK configurations of course!!]. So '30 Dec 2005' (or even 'Dec 30 2005') will be fine

If you are coming from an application language then if at all possible get the date value into a native date-datatype variable - then the application language will "type" the value all the way through to ADO, and it will be unambiguous (that includes the {stuff} formats which cfederl suggested as they are, in effect, "typed" date/time formats). If you will use "string dates" in your application (also applies to text files that will be imported and so on) then I recommend that you only use "yyyymmdd hh:mm:ss.mmm" or "yyyy-mm-ddThh:mm:ss.mmm"

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 01:52:50

Also refer this
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources