Problem extracting date, CR11 1

[azoe]

I have these two parameters in my MS SQL stored procedure:

@StartDate datetime,
@EndDate datetime

If I just insert one of the parameters to the report it looks like this: 6/2/2005 12:00:00AM

The format is System Default Short Format.

What I want is to be able to use just the date part.

I tried this: left({@StartDate}, 2)
and got 20 (I expected 6 or 06 or even 6/).

I thought that meant the year was really on the left but when I put in left({@StartDate}, 4) I got 20-A.

How do I get the day, month, and/or year for my reports?
Thank you -

 

[MrBillSC]

In your formula use the Day, Month, or Year functions.
MrBill

 

[synapsevampire]

Yeah, MS SQL Server doesn't have a date type parameter, pretty annoying, eh?

One solution people use is to ignore the time portion and then in the SP convert the time portion passed.

The other is to use a string type of parameter and then CAST or CONVERT it in the SP to a datetime.

-k

 

[azoe]

So there's not really a way to leave it as is in the SP and deal with it in Crystal? I'm not sure how to do the first solution. I think I already had done one of my SPs with the second one - see below - is that how you do it (I'm obviously no date expert)?
But then I had a problem with creating weekly columns in a crosstab and someone suggested I use datetime from the start - which helped. Also, then I didn't get the little calendar icons in Crystal - which makes it easier for the users to pick dates.
Maybe your first solution would be better (could you give me an example?)
Thank you -

Alter Procedure ...
@StartDateStr varchar(10),
@EndDateStr varchar(10)
As
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

IF ISDATE(@startDateStr) = 1
SET @startDate = CONVERT(datetime, @startDateStr)
ELSE
SET @startDateStr = GETDATE()

 

[ckiklas]

In CR, try using the Date() function instead of Left. Let Crystal worry about where the date is actually located in the field.

so:

Date(WhateverYourDateTimeFieldIs) should do it

 

[azoe]

ckiklas:
I tried that and checked it for errors and Crystal said it was fine. Then I clicked the preview button and it says: "Bad date format string".
Thank you -

 

[azoe]

Sorry - I picked another formula by mistake instead of the parameter - it did work after all.

Then I can use Month(new formula) on that and separate out the month.

Thanks-

 

[synapsevampire]

Ahhh, I misunderstood, I thought you were trying to use a date type as the parameter, glad it worked out.

-k