Convert Date with T-SQL

Hi,

problem with transact-SQL (SQL2k). I'm gathering informations from2 different tables and the column that interest me (the invoice date) have same format but not filled in the same. One is an old table and the format is "datetime" (but written like dd/mm/yyyy) and the new one "datetime" as well but "dd/mm/yyyy hh:mm:ss" (and it's not small datetime)
I'd like to convert the date format wich appears with the "hh:mm:ss" to a smaller format (just dd/mm/yyyy).
I'v tried with the conversion into text (the CONVERT function limits) wich works but I need to deal with a date format when I get the result. If I re convert to smalldatetime, I get the hh:mm:ss again.

Hope this clear.

ThanksDDL and some sample data would be a BIG help...|||SQL Server always stores the date and time. There isn't any way to change that.

You can change how your client displays what SQL Server returns. I think that is what you need to address.

-PatP|||Select ShortInvoiceDt = CONVERT(VARCHAR(10), NewerInvoiceDate, 101)
FROM NewerTable

Use the client app to format accordingly. For example, are you using Access? Crystal? VB?|||I'm testing differents client apps, like Business Object and Harry soft as the data are re stored in a datawarehouse DB.

And yes, seems I don't have the choice, deal with the client App.

Brett, Do you need more details like example?

Thanks for your posts, I'll spare time now searching on the wright place.

Cheers|||In addition to the other reply's,
maybe this will also help some people who read this topic...
------------------
declare
@.invoice_date datetime,
@.new_date varchar(10)

set @.invoice_date = getdate()
-- 'Cut' the time part
select @.new_date = convert(varchar,@.invoice_date,103)
-- Display date
select @.new_date as new_date
-- Work with the date in date format with the time set to zero and convert it later to character for displaying
-- only the date without time part
select convert(datetime,@.new_date,103) as working_date