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

drill-down in excel format

dear all,

anyone know how to make a report using RS to create a drill-down with chart in excel format?

meaning i need a report showing in chat format in excel. and then i also able to drill-down the report. and the chat figure will change follow the drill.

Hello,

The Excel renderer generates charts as static images, not as Excel charts. If you want live Excel charts that update when you change the source data, you should use SoftArtisans OfficeWriter (http://officewriter.softartisans.com).

-Chris

|||

tat is a tools to generate report right?

i not looking for a tools, but i looking a way to settle it. because i got the reporting service at my company already. management will not spend other fund to buy other software.

so any walkaround?

|||

There is no workaround without a third-party tool. The native Excel renderer always exports charts as static images. Even if the Excel renderer did export real Excel charts, Excel itself doesn't support the notion of hyperlink actions when clicking on parts of a chart.

-Chris

|||

so this mean only can use the third-party tool to read data from the database directly?

so in this case the microsoft RS is not able to use. am i right?

|||

Now I'm confused about what your goal is. What exactly is your requirement? Can you please explain it again?

I thought you were trying to export charts to Excel that you can click on and be drilled through to different targets depending on where in the chart you clicked.

What do you mean "read data from the database directly"?

-Chris