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

drop down calendar

Has anyone come up with a way to get a drop down calendar
when entering date ranges for a report? TIA RonTake a look related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=76d653b6-5b05-4cc1-8cae-64c9c410c41a&sloc=en-us
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:5c2001c48142$6d8da300$a601280a@.phx.gbl...
> Has anyone come up with a way to get a drop down calendar
> when entering date ranges for a report? TIA Ron

Drillthrough Query

I have the following Drillthroug query. i require it to return only those documents that fall within a given date range for a particular customer and worktype.

But all the records for a customer and worktype are returned irrespective of the date

DRILLTHROUGH maxrows 10000

SELECT [Measures].[TAT] ON 0 ,

([Hierarchy].[CustomerName].[ABC], [Hierarchy].[Work Type Name].[EEG] ) on 1

FROM ( SELECT ([Date].[Short Date].[2004-03-15] : [Date].[Short Date].[2006-04-10]) ON COLUMNS FROM [DW]) Return[$Customer].[CustomerName] As [CustomerName],[$Measures].[TAT] AS [TAT],[$Document].[Document ID] AS [Document ID]

Hello Reena,

First of all bad news: AS 2005 doesn't allow DrillThrough to be performed on multiple cells, so unfortuantly you'd need to generate multiple drillthrough statements to retrive details for all those dates.

Secondary, why this query doesn't return an error, but doesn't take subselect into consideration. The issue is that drillthrough is performed on the current coordinate, but subselect doesn't affect the current coordinate and the current coordinate for the time dimension in this case is a default member of time - ALL. So, drillthrough is performed on the cell corresponding to the ALL and not to the range.

Hope this helps,

Irina

Drill Through Report -- Passing Date Parameters -- resulting in rsReportParameterTypeMismatch

All,

I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:

4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)

which displays correct data.

However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:

7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)

The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).

I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?

Your help is much appreciated.

Thanks.

Moved to Reporting Services forum.|||I thought I had posted it in SSRS forum? Anyways thanks.|||

People from Microsoft...Would you mind explaining this so very prevalent and confusing issue of Date time formatting? How to get around this issue of reporting services converting the date time to US format?

Just like the below post, I am using Canadian english as the regional settings. In the following post, someone says similar to what I am suggesting, Why does it matter when the datatype being used is DateTime all along and not string, then there shouldn't be any formatting issues? But the SSRS throws an error anyway.

http://www.eggheadcafe.com/forumarchives/SQLServerreportingsvcs/Jan2006/post25361489.asp

If someone has a solution to this, please advise.

|||

I found a related post discussing a similar issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1

I think the reply by Lukasz P. in that thread may be the solution. Has any one tried it?

Drill Through bringing back too many results

I have created a Matrix Report through the Report Builder and specified a filter on a Date field so it only shows the current months work. This works for the initial Totals within the Matrix but when you click on the Totals to show the Drill Down it will show all the results regardless of the Parameter.

How do I make the results in the Drill Through look at the Parameter I am passing to the Report?

Many Thanks

I'm not sure I understand your scenario, and I can't repro the behavior you describe using my best guess. Can you clarify:

1. What fields are in your report?

2. Did you actually create a report parameter by setting Prompt on the filter condition, or did you just create a normal report filter?

Any other info you can provide to further clarify your scenario would be helpful. Thanks!