SQL Server 2008 - date format issue while inserting

Question

I am trying to insert date into a table but the date and format of inserted date is messed up. The datatype in the table is Date. My insert script is as below.

insert into Trans(ID, TDate, Description)
values(1, CONVERT(datetime, 25-02-2012, 101), 'Opening')

I am trying to insert in dd/MM/yyyy format and I want it in the same format in my table. But in my table the date is 1894-07-22 !!

I want the date to be inserted exactly as the format I wish and I want to see the inserted date as 25-02-2012 in the table.

What is wrong here ? Can somebody help ?

Answer 1

Try CONVERT(datetime ,'25-02-2012', 103)

• use some single quotes around the value
• choose your format accordingly (101 is US, meaning mm/dd/yyyy)
• see http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx for more details