lardymonkey lardymonkey - 8 months ago 49
SQL Question

Month returns incorrect value from date


I have an issue with a SQL server that I could do with some help with.
When I run a select statement with a date range in the where it returns rows from the wrong date range.

e.g. If I execute the following

select * from theTable where startDate between '2016-09-01 00:00:00' and '2016-09-08 23:59:59'

I would expect rows where the
is between 1st and the 8th of September, what I am actually getting is rows with a
between 9th January and 9th August.

I have checked the language I have setup on for the datebase using:

select * from sys.syslanguages order by name where name = @@language

This returns British

DBCC useroptions returns

textsize 2147483647
language British
dateformat dmy
datefirst 1
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

I have checked the default language for the login and this is also British.

When I execute
print month('2016-09-01 00:00:00')

It returns 1 and not 9. Unless I am mistaken the date and time above should be ODBC canonical
yyyy-mm-dd hh:mi:ss(24h)

Why is it printing 1 instead of 9, and more importantly how do I fix it?


Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Mar 25 2011 13:33:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Thank you for your help.
If this has already been answered can you point me at it, only I cannot find the answer.

Answer Source

You would appear to have a relatively unusual internationalization setting, where the default is YDM rather than YMD.

You can always use dates without hyphens:

select *
from theTable
where startDate >= '20160901' and
      startDate < '20160909';

These are always interpreted as YYYYMMDD in SQL Server.