Drop the time portion of a smalldatetime field

In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
instead of 1/31/2004 10:30:25 AM
How can I do this in a stored procedure?
lqI'm not sure if this is the *cleanest* way, but you could do
CONVERT(SMALLDATETIME, myDateField, [100-1??]). If you look at the
online help for "CONVERT", you'll be able to see the optional paramater
at the end that allows you to format the data however you want. It is a
number that corresponds to a format. I use 102 which equals
'yyyy.mm.dd', but there are many others. Hope that helps.

Lauren Quantrell wrote:
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401271927.585c8a97@.posting.google.com>...
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq

See the CONVERT() function in Books Online:

select convert(char(10), MyColumn, 101)
from dbo.MyTable

But in general you should format dates in your client application if
possible, as it's easier to handle different client regional settings
etc.

Simon