Question about formatting a date

I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:

select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..

Here is my explanation:

1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.

I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!

Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:

SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))

Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.

Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P

What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:

select convert(char(10), convert(datetime, '20040119'), 101)

The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:

select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)

101 - MM/DD/YYYY
103 - DD/MM/YYYY

Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.