DateTime Problem in SP

I am getting this error in the SP shown below and don't see what is wrong?
Syntax error converting character string to smalldatetime data type.
The complete output is as follows:
---
DECLARE @.RC int
DECLARE @.Class char(2)
DECLARE @.StartDate datetime
DECLARE @.EndDate datetime
DECLARE @.Period varchar(10)
SELECT @.Class = 'SW'
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
SELECT @.Period = 'Test'
EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
@.Period
DECLARE @.PrnLine nvarchar(4000)
PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
PRINT @.PrnLine
---
=============== SP Code ================
@.Class As char(2),
@.StartDate As SmallDateTime,
@.EndDate As SmallDateTime,
@.Period As Varchar(10)
As
Set NOCOUNT ON
DECLARE
@.strSQL As varchar(1000)
SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
''' + Cast(@.EndDate As SmallDateTime) + ''
Print @.strSQL
INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
exec(@.strSQL)Maybe your system is set up for UK English, or some other regional settings,
or some other language.
How about we try a sensible and unambiguous date format, like YYYYMMDD.
SELECT @.startDate = '20050205', @.endDate = '20050213'
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:O9NBn5OKFHA.3420@.tk2msftngp13.phx.gbl...
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>|||Well for one thing You have "=>" In there, and that's wrong, it should be
">=".
Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
month... depending on server settings... A format that always works is
CCYYMMDD, or, for Feb 13, 2005,
'20050213'
try changing the string literals
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
to
SELECT @.StartDate = '20050205'
SELECT @.EndDate = '20050213'
and see if it works then...
"Wayne Wengert" wrote:

> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Wayne Wengert wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Your WHERE clause should be like this:
WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
Since you've already declared the parameters @.StartDate & @.EndDate as
SmallDateTime data types you don't have to do it again w/ the Cast()
function. What you have to do, since you're putting the date values in
a string, is convert them to string data types. In my example I used
CHAR(8) to just get a date like this '20040314'.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
Fnny1XBrRp8n15Q7xe4Mm6VR
=GT97
--END PGP SIGNATURE--|||And Oh, replace "=>" and "=<" with ">=", and "<="
"Wayne Wengert" wrote:

> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Thanks - I figured that out (finally)
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:849DC6A0-15EF-4616-BC47-555F941C4199@.microsoft.com...
> Well for one thing You have "=>" In there, and that's wrong, it should
be
> ">=".
> Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
> month... depending on server settings... A format that always works is
> CCYYMMDD, or, for Feb 13, 2005,
> '20050213'
> try changing the string literals
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> to
> SELECT @.StartDate = '20050205'
> SELECT @.EndDate = '20050213'
> and see if it works then...
>
> "Wayne Wengert" wrote:
>
wrong?
WHERE
=<|||Thanks - that was what I forgot!
Wayne
"MGFoster" <me@.privacy.com> wrote in message
news:6tpZd.10908$cN6.9661@.newsread1.news.pas.earthlink.net...
> Wayne Wengert wrote:
wrong?
WHERE
=<
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
> function. What you have to do, since you're putting the date values in
> a string, is convert them to string data types. In my example I used
> CHAR(8) to just get a date like this '20040314'.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
> Fnny1XBrRp8n15Q7xe4Mm6VR
> =GT97
> --END PGP SIGNATURE--|||
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
This still could lead to an error if his settings are, say, UK English, and
he says
SET @.startDate = '2/16/2005'

> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
Neither do you have to do a CONVERT at all in this case (if he uses YYYYMMDD
in his SET/SELECT then it's already in 112 format), and nor do you have to
surround the date value with strings like you did. This will be sufficient:
WHERE ShowDate >= @.startDate
Finally, more for Wayne than the others, be careful how you define the end
date. If you say <= <somedate_notime> you will include rows with a value of
midnight on that day, but not 12:01 AM or 3:45 PM. If you only have
midnight timestamps in the data then it's no big deal, but if you don't
constrain the data, you're better off using < (@.endDate + 1).
A

DateTime Bugs?

Hi,
Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
2006 2:00PM') '
TEST
--
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
OUTPUT
--
2
Changed language setting to Espaol.
Ene 19 2006 9:58PM
Ene 16 2006 2:00PM
1
1
Changed language setting to us_english.
Thanks,
KennyI believe it is something to do with which day of the w to be considered
as first day. As default (English) it is Sunday.
If you issue SET DATEFIRST 7 (7 represents Sunday) just before DATAEPART
function, it should solve your "bug".
print DATEPART(dw,'Jan 16 2006 2:00PM')
SET LANGUAGE spanish
print getdate()
declare @.datetime datetime
set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
print @.datetime
SET DATEFIRST 7
print DATEPART(dw,@.datetime)
print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
SET LANGUAGE us_english
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
> for spanish datetime is 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16
> 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>|||Microsoft failed to follow ISO standards about day of the wek numbers.
They also wrote their own version of ws-within-year numbers.|||What are you talking about? 8601 was not even out until 1988 and was not
popular until second version in 2000. Sybase was created before that.
Also, check the calendar on your desk. It starts with Sunday. People were
using start of w on Sunday long before ISO. Besides, you can change the
start day anyway.
William Stacey [MVP]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137739565.063520.234520@.g14g2000cwa.googlegroups.com...
| Microsoft failed to follow ISO standards about day of the wek numbers.
| They also wrote their own version of ws-within-year numbers.
||||Hello, Joe
Indeed, the w numbers returned by the DATEPART are not the ISO w
numbers. There is an example in Books Online on how to create a UDF to
return the ISO w number. However, the original poster was talking
about wdays, not w numbers (which is a completely different
thing).
Razvan|||As indicated in other posts, day of w is dependent on which country you l
ive in. In the US,
Sunday is the first day of the w. In Sweden (and majority of Europe, prob
ably all), first day of
w is Monday. DATEPART to calculate day of w is dependent on SET LANGUA
GE and can be overridden
with SET DATEFIRST.
set language us_english
print DATEPART(dw,getdate())
set language british
print DATEPART(dw,getdate())
set language spanish
print DATEPART(dw,getdate())
set language polish
print DATEPART(dw,getdate())
set language german
print DATEPART(dw,getdate())
set language swedish
print DATEPART(dw,getdate())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kenny" <keejh@.hotmail.com> wrote in message news:%23XFnc6WHGHA.3936@.TK2MSFTNGP12.phx.gbl..
.
> Hi,
> Below shown simple script to get the wday. Any idea why the wday
for spanish datetime is
> 1 instead of 2 for 'Ene 16 2006 2:00PM' ('Jan 16 2006 2:00PM') '
> TEST
> --
> print DATEPART(dw,'Jan 16 2006 2:00PM')
> SET LANGUAGE spanish
> print getdate()
> declare @.datetime datetime
> set @.datetime = convert(datetime, 'Ene 16 2006 2:00PM', 121)
> print @.datetime
> print DATEPART(dw,@.datetime)
> print DATEPART(dw,convert(datetime, 'Ene 16 2006 2:00PM', 109))
> SET LANGUAGE us_english
> OUTPUT
> --
> 2
> Changed language setting to Espaol.
> Ene 19 2006 9:58PM
> Ene 16 2006 2:00PM
> 1
> 1
> Changed language setting to us_english.
> Thanks,
> Kenny
>

datetime + sp2?

In report designer using the data tab when testing the datetime parameters
the datetime is being shown as UK locale and the data is being rendered
properly (UK locale). However, if I then switch to the preview tab the
datetime parameters are being formatted US locale. If I deploy the result is
the same, US style locale.
I have set the report language to English UK the machine locale is set to
UK, IE is set to en-gb. What can I do next?
I was wondering if this is an sp2 issue as sp1 fixed the locale issues.
Thanks
FrankWhat is the VS languge set to?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Ashley" <aa@.aa.com> wrote in message
news:eCL9Mj2SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> In report designer using the data tab when testing the datetime parameters
> the datetime is being shown as UK locale and the data is being rendered
> properly (UK locale). However, if I then switch to the preview tab the
> datetime parameters are being formatted US locale. If I deploy the result
> is the same, US style locale.
> I have set the report language to English UK the machine locale is set to
> UK, IE is set to en-gb. What can I do next?
> I was wondering if this is an sp2 issue as sp1 fixed the locale issues.
> Thanks
> Frank
>
>|||VS2003 is set to
Tools | International Settings | Language | Same as Microsoft office
(language neutral)
Office 2003 Enabled langauge is set to English (UK) and default behaviour is
set to English UK.
Hope this helps.
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%23n2x5x6SFHA.3188@.TK2MSFTNGP09.phx.gbl...
> What is the VS languge set to?
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Frank Ashley" <aa@.aa.com> wrote in message
> news:eCL9Mj2SFHA.2560@.TK2MSFTNGP09.phx.gbl...
>> In report designer using the data tab when testing the datetime
>> parameters the datetime is being shown as UK locale and the data is being
>> rendered properly (UK locale). However, if I then switch to the preview
>> tab the datetime parameters are being formatted US locale. If I deploy
>> the result is the same, US style locale.
>> I have set the report language to English UK the machine locale is set to
>> UK, IE is set to en-gb. What can I do next?
>> I was wondering if this is an sp2 issue as sp1 fixed the locale issues.
>> Thanks
>> Frank
>>
>>
>|||Can you try a non-English locale? I'm stumped.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Ashley" <aa@.aa.com> wrote in message
news:uUaOA$7SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> VS2003 is set to
> Tools | International Settings | Language | Same as Microsoft office
> (language neutral)
> Office 2003 Enabled langauge is set to English (UK) and default behaviour
> is set to English UK.
> Hope this helps.
> Frank
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:%23n2x5x6SFHA.3188@.TK2MSFTNGP09.phx.gbl...
>> What is the VS languge set to?
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Frank Ashley" <aa@.aa.com> wrote in message
>> news:eCL9Mj2SFHA.2560@.TK2MSFTNGP09.phx.gbl...
>> In report designer using the data tab when testing the datetime
>> parameters the datetime is being shown as UK locale and the data is
>> being rendered properly (UK locale). However, if I then switch to the
>> preview tab the datetime parameters are being formatted US locale. If I
>> deploy the result is the same, US style locale.
>> I have set the report language to English UK the machine locale is set
>> to UK, IE is set to en-gb. What can I do next?
>> I was wondering if this is an sp2 issue as sp1 fixed the locale issues.
>> Thanks
>> Frank
>>
>>
>>
>|||Hi Brian,
I changed the locale to Finnish and the dates were rendered as dd.mm.yyyy
hh:mm:ss in the data tab but still the drop down on the preview tab showed
mm/dd/yyyy hh:mm:ss
I then tried removing Reporting Services and re-installing (going straight
to sp2, bypassing sp1) but still the same results.
regards
Frank
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:u1c$ioNTFHA.2828@.TK2MSFTNGP10.phx.gbl...
> Can you try a non-English locale? I'm stumped.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Frank Ashley" <aa@.aa.com> wrote in message
> news:uUaOA$7SFHA.1044@.TK2MSFTNGP10.phx.gbl...
>> VS2003 is set to
>> Tools | International Settings | Language | Same as Microsoft office
>> (language neutral)
>> Office 2003 Enabled langauge is set to English (UK) and default behaviour
>> is set to English UK.
>> Hope this helps.
>> Frank
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> news:%23n2x5x6SFHA.3188@.TK2MSFTNGP09.phx.gbl...
>> What is the VS languge set to?
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Frank Ashley" <aa@.aa.com> wrote in message
>> news:eCL9Mj2SFHA.2560@.TK2MSFTNGP09.phx.gbl...
>> In report designer using the data tab when testing the datetime
>> parameters the datetime is being shown as UK locale and the data is
>> being rendered properly (UK locale). However, if I then switch to the
>> preview tab the datetime parameters are being formatted US locale. If I
>> deploy the result is the same, US style locale.
>> I have set the report language to English UK the machine locale is set
>> to UK, IE is set to en-gb. What can I do next?
>> I was wondering if this is an sp2 issue as sp1 fixed the locale issues.
>> Thanks
>> Frank
>>
>>
>>
>>
>

Dates get alphabetized when report is shown

I've built a report from a cube that I have had made. After selecting a few dimensions, the columns will be showing a drill down action related to different dates. Problem is, when you preview the report, the dates get alphabetized; they don't show up in an order like dates, days should.

ex: monday, friday, thursday, tuesday, wednesday

or april, august, july, june, may

How can this be changed, or is it related to the dimensions in the way they were made? Possibly something from the tables then? If more information is needed, please specify.

Im running Sql 2005 Developer Edition, with BIDS.

Sounds like your dates are set up with a string data type so you'll want to change that in the database/cube or you could try a cdate() in reporting services to convert it to a date (e.g. cdate(Fields!Month.value, "MMM"). But if you're going to use the cube alot (and the dates) it would be better to alter it in the back end.

Regards,

Ali

|||Do the dates come back in that order in the query designer? If so, you need to go back to the cube, and change your dimensions to order properly. You can do that by using the OrderBy property on the dimension attribute. If you are using a numeric key that has the proper order (1 for Quarter 1, 2 for Quarter 2, etc.), you can order by the key. If not, you can add a related attribute that contians the proper sort order, or you may need to redesign the dimension somewhat.|||

I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.

Shyam

|||

Shyam Sundar wrote:

I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.

Shyam

yes I am using a matrix, but Im not sure how to do this.

I am new to sql 2005, and all of these suggestions thus far sound good. I will be on the phone with these specifics to my associate who made the cube. I'll need to have those dimensions tweaked some more. Thanks!

I'll report back as to what the issue turned out to be.

|||

Right click on the appropriate row group cell and click on Edit Group. Go to Sorting tab and select the date field under Expression and select Ascending under Direction.

Shyam