• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1080
  • Last Modified:

Crystal Reports - Stored Procedure Datetime Format

I need to use a stored procedure (MSSQL) that has a datetime parameter.  When I add it to the report, the parameter shows up nicely, but is there any way that I can make the parameter inside Crystal Reports just a date parameter instead of a datetime?  If I run a report now, I'd like to see the date passed to the procedure as '04/15/2010 12:00:00' instead of '04/15/2010 10:27:00'
0
michaeldepasquale
Asked:
michaeldepasquale
  • 6
  • 3
  • 3
  • +1
1 Solution
 
Shannon_LowderCommented:
I usually either make the parameter of the stored procedure a VARCHAR representing the datetime, and limit it to a certain format say mm/dd/yyyy, or immediately inside the procedure strip the time out of the parameter before using it.

The second option is usually the better option, in my experience.
---
Shannon Lowder
Database Engineer
0
 
Shannon_LowderCommented:
Just for grins, here is some code that could strip the time from your parameter inside your stored procedure.

---
Shannon Lowder
Database Engineer
set @reportDate = CAST(
 (
 STR( YEAR( @reportDate ) ) + '/' +
 STR( MONTH( @reportDate ) ) + '/' +
 STR( DAY( @reportDate ) )) AS DATETIME)

Open in new window

0
 
michaeldepasqualeAuthor Commented:
That's pretty much what I've been doing.  Depending on the client, I'll use the datetime parameter and strip the time in the proc.  If the client barks at the time portion in the calendar control, I'll go to the varchar method.

I was hoping for something more elegant, but maybe there isn't anything.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
UnifiedISCommented:
I agree with Shannon, strip the time in the proc.  

I use this to strip time:

CONVERT(varchar(10), @reportDate, 101)

When I offer a date range, I always add a day to the stripped end date so times don't interfere with what the user really wants:

SELECT @StartDate = CONVERT(varchar(10), @StartDate, 101)
SELECT @EndDate = CONVERT(varchar(10), DATEADD(dd, 1, @EndDate), 101)

SELECT...
FROM...
WHERE Field1 >= @StartDate AND Field1 < @EndDate
0
 
michaeldepasqualeAuthor Commented:
The problem isn't on the end date, it's on the start date.  If the data is always stored at midnight - i.e. '2010-01-01 00:00:00:000', then the start date is an issue.  When a user runs the report, it will default the start date to the current datetime and then they will use the calendar control to change the date.  The problem there is that the time portion stays, so the datetime sent to the proc is '2010-01-01 09:00:00:000' which would exclude the first day of the range.

My solution has always been to strip the time in the proc and set it back to midnight.  If I use just views or tables, I can trick Crystal into using Date parameters for Datetime fields, but I haven't found a way to do it when a proc is used.
0
 
UnifiedISCommented:
I understand.  I haven't found a way either.  I just tell the users to ignore the time portion and I strip the date.  I was just adding a technique I use for date ranges with crystal and offering an alternative method to stripping the time.  
0
 
mlmccCommented:
How are you passing the date to the report or the stored procedure?

mlmcc
0
 
michaeldepasqualeAuthor Commented:
When you attach a proc to Crystal, it reads the parameters automatically and you can't change the data type of a proc parameter in Crystal.  So to answer, I guess it's to the proc.
0
 
mlmccCommented:
My question was in regards to are you using a program to call the report and pass th eparameters or just the Crystal parameter screen?

mmlcc
0
 
michaeldepasqualeAuthor Commented:
I see - It's being called from an application, but the users are entering the parameters.
0
 
mlmccCommented:
Can you change the application to get the date then add the time?

mlmcc
0
 
Shannon_LowderCommented:
In the application is the date entry a text field, or is it a calendar object?
---
Shannon Lowder
Database Engineer
0
 
michaeldepasqualeAuthor Commented:
Sorry, I should have been more clear.  The application simply provides the connection information and a menu structure.  Once the user clicks on the icon, it launches Crystal run-time and Crystal controls the prompts at that point.

I could write an executable to do the parameters and have more control that way, but based on the number of reports I write that would be a huge task.
0
 
UnifiedISCommented:
It's not as huge a task as it might seem.  Because you have a limited number of datatypes available, you can cycle through the reports' parameters and based on type, prompt the user with a simple input screen.  The crystal objects are a little tricky to navigate but once you get it, you open up some wonderful functionality.

for each parameter
select paramater type
case date
'prompt user with a form with a datepicker
case string
'prompt user with a form with a text box
next


For this question though, if the stored procedure has a datetime parameter, Crystal will require date and time.  To be safe, you should always strip the time in the proc if you don't want time.  using a varchar parameter means you'll have to validate the users' entry and reports don't offer good solutions for responding to bad entry.
0
 
michaeldepasqualeAuthor Commented:
In most cases you'd be 100% correct.  The reports I'm writing are part of a financial accounting system and in order to create executables that are launchable from the menu, I need to use their application framework which is a complete nightmare.  It would take me an hour to write the report and 3 hours to do a parameter executable as opposed to just letting Crystal do the work.

The way I'm doing it today (either stripping the time or using a varchar) works, I was just hoping for a miracle solution that I didn't think of.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now