DateTime format

Hi,
I'm new to SQL Server (Express) and I wonder if there is a way that I can format a date's appearance in the database, that is, the format of the datetime column.

When I view a date in VWD Express, it's in my country's format (2006-11-24 for example) but when I try to insert a date using the same format using a web form, the inserted date in the database becomes 1905-06-something. This happens regardless of whether I'm inserting a string or if the string has been converted to a date via CDate.

So, is there a way I can set the database's date format? And why is it wrong anyway? It's bugging me as the original (Swedish) date is already in the ISO format that SQL Server seems to use (such as yyyy-mm-dd), and I'm using localhost with Windows set to Swedish, IE 6 set to Swedish, and even web.config's UICulture and Culture to Swedish as well.

Of course, I can rearrange the order of the date's numbers to get proper values in the db, but it seems as an unnecessary step and I can't figure out what format to use anyway.

All help is very welcome.

Pettrer

You can use a SQL session option 'DATEFORMAT' to set the date format for your connection to SQL, instead of rearranging the order of the date's numbers. Please check this post:

http://forums.asp.net/thread/1262753.aspx

|||

When inserting to a table with a datetime column the best way is always to use a parameterized query/sp and feed it a datetime value. Never do any string formatting in your code before insert.

Although it "should work" :) so therefore I ask you - remembered quotes around date? i.e

insert into blabla select 1, 2, '2006-06-12'

|||

Iori_Jay:

You can use a SQL session option 'DATEFORMAT' to set the date format for your connection to SQL, instead of rearranging the order of the date's numbers. Please check this post:

http://forums.asp.net/thread/1262753.aspx

Iori_Jay,

Thanks for the tip! It's really cool.

Pettrer

|||

aspcode.net:

When inserting to a table with a datetime column the best way is always to use a parameterized query/sp and feed it a datetime value. Never do any string formatting in your code before insert.

Although it "should work" :) so therefore I ask you - remembered quotes around date? i.e

insert into blabla select 1, 2, '2006-06-12'

Ehrm... I must admit I didn't know they were needed... (I've only used MS Access before.)

That did it! Thanks a bunch!

Pettrer

|||

Great it worked out. Done the same mistake myself :) Without quotes SQL Server first calculates the value, meaning 2006-06-12 becomes 1988 and that is interpreted as days added to sql server mindate or something.

Ex

select convert(datetime, 2006-06-12) --> 1905-06-12 same as
select convert(datetime, 1988) --> 1905-06-12

select convert(datetime, '2006-06-12') --> 2006-06-12

|||

Well, yeah, that nailed it! ;-)

Thanks

P