What IS going on..

[xloop]

Some weird date problems..

I have a table with a date field, if i open the table the dates are displayed like they should be dd/mm/yyyy 00:00:00

If i do a simple select * from table statement then the dates are displayed like 2003-3-1 00:00:00

However, i do a select * from table where tdate BETWEEN @date1 and @date2 then everything suddenly goes all american !

It will work if the date is 3/1/2003 but not 1/3/2003, but opening the table and looking it's perfectly correct format.

Regional setting are fine, it's set to british english in server settings.

How frustrating, anyone?

 

[tlbroadbent]

How are you opening the table? What programs are you using? The various displays you see are the result of the program that produces the display not the way dates are stored in SQL Server. Dates aere stored as numbers. Each program you use to display dates formats it according to its own settings. Check the following resources.

faq183-3007 - Why Does SQL Server Display My Data Like That?

Handling Dates in SQL Server

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm