dd/MM/yyyy in SQL Server

Question

I have a DATETIME column "EntryDate" and I am storing values as dd/MM/yyyy format. But when I fetch records through SQL I don't get the expected result.

For ex:

I have 90 records from date 23/10/2009 to 23/11/2009. I get only 5-10 records. Why is this ?

Here is my query :

SET DATEFORMAT DMY;
SELECT * FROM salesorderlist so 
LEFT JOINT sites s ON s.id = so.siteid
WHERE so.entrydate between '23/10/2009' and '23/11/2009' 
ORDER BY so.entryDate DESC

But when I give date range from 23/10/2009 to 31/10/2009 I get the correct result, and if I give date range from 23/10/2009 to 01/11/2009 I don't get even a single row.

What am I missing ?

In database I am storing values as "31/10/2009" (dd/MM/yyyy) format.

Thanks in advance.

Have done a blunder..
Entrydate is Varchar Column. Now changed it to Datetime.Thanks Guys for your help

I have a bigger problem now...How do i convert varchar column to datetime column...how to update the table

When i try to change the datatype i get the error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.

I dont want to delete records as there are thousands of records.Is there any way to convert ? Thanks

Answer 1

It looks like you're storing your dates in a text format, in which case the returned resultset is correct (since 23/11/2009 comes pretty much directly after 23/10/2009 if you are sorting same-length strings alphanumerically).

Unless you have a good reason for not doing so, you should really always store dates as dates, numbers as numbers etc.etc.

Answer 2

I guess you are storing the data as varchar.

In a string comparison, '23/10/2009' and '23/11/2009' are identical to the 5th character.

You should

• Use yyyymmdd for date literals (yyyy-mm-dd for new SQL 2008 ones)
• Use one of the date+time datatypes

Answer 3

Just for your information: assuming that the column is actually of type DateTime (other people has suggested that maybe you are using a text column), you don't "store date values in X format"; the date values are stored in the database using an internal format. Date format comes to play only when retrieving data.

Having said that, do you have time values in your dates? If you have dates corresponding to 23/11/2009 but with a time value different from 0:00:00, these will not show up in your query. You should specify something like '23/11/2009 23:59:59.999' instead.

Anyway, it is generally not a good idea to construct queries like this, hardcoding the parameters for the where clause in the query itself (this is prone to errors and you may inadvertently open the door to dependency injection attacks). Instead, you should use the parameters mechanism of the database access engine you are using (if you use ADO.NET, see for example here: http://www.csharp-station.com/Tutorials/AdoDotNet/lesson06.aspx)

Answer 4

Unless you give a time it will use 12:00 AM at the very start of the day so "dates < 23/11/2009" will not include any entries on the 23rd. Does that help?

Maybe your join isn't generating what you think it is - try looking at the intermediate table before restricting it by date.

If you post the data from your table I could try to help some more.

Answer 5

If you are storing your dates as DATETIME data type, then I would try to use the ISO-8601 date format (YYYYMMDD) for any queries:

SELECT ......
WHERE so.entrydate BETWEEN '20091023' and '20091123'

This format is independent of any date format setting, and will always work - no matter how the date is formatted on your SQL Server machine.

Also, you need to remember that DATETIME also stores TIME - so this above query will ONLY return those records that are up to just 0:00 hours on Nov-23, 2009 - it will not show anything during that day!

If you want a BETWEEN query up to and including all of Nov-23, 2009, you need to specify:

SELECT ......
WHERE so.entrydate BETWEEN '20091023' and '20091123 23:59:59'

or just use '20091124' as your second criteria - then you get everything up to and inlcuding the last second of the previous day (Nov-23).

If you're on SQL Server 2008, you could also check out the new DATE datatype which stores date-only - no time - and makes these kind of queries a lot easier! (besides also using up less space).

UPDATE:

To convert your existing table, I would do this:

• create a new column "MyNewDateTime" of type DATETIME

• run a script over your table to convert all the fields:

  UPDATE dbo.salesorderlist 
  SET MyNewDateTime = CONVERT(DATETIME, EntryDate, 103)
  

This should convert your "dd/mm/yyyy" style date strings into DATETIME and store it in the new column. Once that's done, you can drop the old column

ALTER TABLE dbo.salesorderlist DROP COLUMN EntryDate

and rename the new one to the old name (if you want to):

EXEC sys.sp_rename @objname = 'dbo.salesorderlist.MyNewDateTime', 
                   @newname = 'EntryDate', 
                   @objtype = 'COLUMN'

Marc

Answer 6

To convert the column data type I usually do the following

ALTER TABLE salesorderlist ADD entrydate2 datetime
GO
UPDATE salesorderlist SET entrydate2=CAST(entrydate AS datetime)
GO
ALTER TABLE salesorderlist DROP COLUMN entrydate
GO
EXEC sp_rename 'dbo.salesorderlist.entrydate2', 'entrydate', 'COLUMN'
GO

Answer 7

For converting your column, I'd suggest you first change the contents of the column to use an unambiguous format (yyyymmdd or yyyy-mm-ddThh:mm:ss, with obvious meanings, except T is just literally the letter T), and then perform the alter table statement.

Answer 8

Use this -->

SET DATEFORMAT DMY;SELECT * FROM salesorderlist so LEFT JOINT sites s ON s.id = so.siteidWHERE so.entrydate between convert(varchar, '23/10/2009',101) and convert(varchar, '23/11/2009', 101) ORDER BY so.entryDate DESC