sql server dates

Hello, really need some help here!

I've been inserting dates from an asp page into an sql server db in mm/dd/yyyy format, which is what is accepted using smalldatetime. However, when I retrieve a date from the db and display it on my page, it is in dd/mm/yyyy format. This wouldn't be a problem, but if I want to update details in the same table as the date, but keep the date the same, the dd/mm/yyyy format is not accepted. Anyone have any idea what's going on here? sql server seems to accept dates in one format and output them in another...

that is absolutely correct

this is because internally, dates are stored as single integers

(for smalldatetime, it's the number of days since dec 31 1899)

if you wish to see a particular format on output, i.e. the default output format is not suitable, then you will have to use the CONVERT function

e.g. select convert(char(10),yourdatefiled,101) as mm_dd_yyyy

I agree, when inserting dates into MS SQL, always use a CONVERT statement. That way you will never have problems if ie. the region on the SQL server was changed.

To absolutely avoid any confusion I always insert a date in the long form as a varchar/string i.e. '12 December 2004'. When retrieving a date always use convert() so you get it back in the format you need.