MS SQL Convert function not working

I have just converted an Access database to a SQLServer one and now have the joy(!!) of changing all the SQL statements to make then compatible.
In Access I had a Format function around a date, so now I am using a convert function which takes the form of convert(datetime,TABLE_NAME.COLUMN_NAME,n).

No matter what the number n is, the resultant date always comes out the same in m/d/yyyy format. This appears to go against everything I've read so far and cannot for the life of me work out what is going on.

Can anyone help?

Thanks
Desphera

In SQL Server, the 'n' you speak of is the 'style'. What date style are you trying to set? I'll try it.

I've tried most numbers between 101 and 113. See if you have any luck.

You're right. I tried:

SELECT CONVERT(char(11), '12/12/2003', 111) As NewDate

...and it didn't work... Let me do some more research.

Go to this page, I tried teh first example, and it worked...

http://www.databasejournal.com/feat...10894_2197931_2

Still, I tried some other things and it didn't work as expected...

See if that helps.

I just tried
SELECT * ,CONVERT(CHAR(19),'2003-01-22 10:31 PM',106)
FROM TABLE_NAME and it just returned '2003-01-22 10:31 PM' with each row. Tried it again with 111 and the same thing happened.

Is there perhaps something wrong with my installation of SQL Server?

I'm beginning to get frustrated with SQL Server already and I've only been using it a day or two. Come back Oracle, Access and MySQL, all is forgiven, or at least they do what the instructions say on the packet!!!

dcarva - thanks for your ideas but I'm still getting nowhere with this one, no matter which forums or sites I try to find answers on.
Just doesn't seem to make sense - anyone else out there got any ideas?

I've found the problem......

The first parameter to the convert function is the datatype of the output string NOT the input column/second parameter).

There at last!!!!!

Good job. Can you show me the code you used?

These now work fine

select CONVERT(varchar(11),FROM_DATE,106) FROM TABLE_NAME

or

select CONVERT(varchar(10),FROM_DATE,111) FROM TABLE_NAME

I cannot get the Convert function to work at all

sending SQL statements in ASP.NET against an Access
2000 database. I keep getting an "undefined function" message.
In fact the only functions I can get working are DAY, Month, and Year,
but I have not been able to concatenate them to produce a dd/mm/yy
result.
Anyone got any suggestions? I would appreciate it.
Bill Hay

When you query an Access 2000 database, the concatenation
operator seems to be & instead of +.
Haven't seen this in any of the stuff I've read, but that's how I got
my queries to work.
Bill Hay