Date Problem

I have a problem with date parameter. I have selected data type as date time. I would like the date to be in British format. When I select a date from Calendar control, for example 25 December, then I get a error>

The value provided for the report prameter Startdate is not valid for its type.

Can anyone please help me on this?

regards

Josh

Hello JoshKer.

In Reporting Servies you can define the default Language of the reports, this Language is used for the Date Culture...

To edit him : In Layout Tab / Click on the Yello Part (out of the report) / Properties or F4 / then you accessed the Report Properties...

Alter the Language Propertie, to English (United KingDown)

If this not correct your error, then you can make the CAST on the Date Parameter on the Query....

I Hope Have helped you !

|||See if the problem is dedicated to this entry, if yes, vote for it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for your reply. But it gives the same problem with the date picker.

Date Parameter

Hello ........

I have a DateTime parameter in my report.

I want the last date of the current month selected as default in the date parameter(datetime picker ).

Can anybody help me ?

Thanks

Hi ecn i,

You could write an expression\custom code block to set the parameter default value to be the last day of the current month.

|||

Thanks for your reply.

Please give me the example (expression) for finding the last date of the current month

and set it as default.

|||=DateSerial(Year(Now()),Month(Now()) + 2,0)
Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||

Thank You Very Much For Your Reply .

It Really help me a lot.

|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||

To get the last day of this month, get the first date of next month, then subtract one day from it.

This expression gets the first day of next month:

Code Snippet

=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")

Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:

Code Snippet

=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))

This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")

If you want to avoid a Format statement then you can use the Month and DatePart functions:

Code Snippet

=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))

Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.

|||

Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")

Date Parameter

Hello ........

I have a DateTime parameter in my report.

I want the last date of the current month selected as default in the date parameter(datetime picker ).

Can anybody help me ?

Thanks

Hi ecn i,

You could write an expression\custom code block to set the parameter default value to be the last day of the current month.

|||

Thanks for your reply.

Please give me the example (expression) for finding the last date of the current month

and set it as default.

|||=DateSerial(Year(Now()),Month(Now()) + 2,0)
Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||

Thank You Very Much For Your Reply .

It Really help me a lot.

|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||

To get the last day of this month, get the first date of next month, then subtract one day from it.

This expression gets the first day of next month:

Code Snippet

=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")

Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:

Code Snippet

=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))

This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")

If you want to avoid a Format statement then you can use the Month and DatePart functions:

Code Snippet

=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))

Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.

|||

Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")

Date Parameter

Hello ........

I have a DateTime parameter in my report.

I want the last date of the current month selected as default in the date parameter(datetime picker ).

Can anybody help me ?

Thanks

Hi ecn i,

You could write an expression\custom code block to set the parameter default value to be the last day of the current month.

|||

Thanks for your reply.

Please give me the example (expression) for finding the last date of the current month

and set it as default.

|||=DateSerial(Year(Now()),Month(Now()) + 2,0)
Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||

Thank You Very Much For Your Reply .

It Really help me a lot.

|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||

To get the last day of this month, get the first date of next month, then subtract one day from it.

This expression gets the first day of next month:

Code Snippet

=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")

Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:

Code Snippet

=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))

This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")

If you want to avoid a Format statement then you can use the Month and DatePart functions:

Code Snippet

=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))

Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.

|||

Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")

Date Formula Current Month +1

How can I limit a report so that it only shows where the selected date (for example dateX) is greater than month(currentdate)?

For example, this is March 2006 so I would want to show everything from April 2006 and out as far as the data goes.

I tried using the formula:

month(dateX) > month(currentdate)
but then I get don't get data from January and February of 2007.

If I use:
(month(dateX) > month(currentdate) or
year(dateX) > year (currentdate))
I get data from January 2001 etc.

Another problem is that this data needs to be in a cross tab so I can't just use section expert to supress records.

Can anyone help me??

Thanks,
Babs827I don't know wether it will work for you or not and might be you get better formula for that but just try this :

define new formula field : LAST_DATE like

DateAdd ("m",1 ,CurrentDate ) - DatePart ("d", CurrentDate)

And then in your report condition give DATEX > LAST_DATE

if it work out for you let me know

Best Luck|||Do you mean to put the formula below in the selction criteria?

WhileReadingRecords;
DateVar Last_Date;
Last_Date := DateAdd ("m",1 ,CurrentDate ) - DatePart ("d", CurrentDate);
DateX > Last_Date

I tried this and it says that a date is expected where this 'DateAdd ("m",1 ,CurrentDate ) - DatePart ("d", CurrentDate);' is located.

Is there another way to arrange it?|||Nope,

In crystal report go to INSERT -> FIELD OBJECT -> FORMULA FIELD and define new formula field LAST_DATE

and in LAST_DATE formula put :
DateAdd ("m",1 ,CurrentDate ) - DatePart ("d", CurrentDate)

and save it ...

Now right click on DATEX (Your date on crystal report which comes from any table or runtime) go to SELECT EXPERT and in FORMULA EDITOR write

DateX > @.Last_Date|||That seems to work great, Thank you so much!!!!|||Hi all

I went through thread
nice to see this

I am also struggling with same kind of stuff

My query is

I need to go back for one year from selected date range
month and date is fixed like 01/06(dd/mm) and year goes to previous year

i mean
if i selected daterange of 05/06/2006 - 06/06/2006 than i get the values of 01/06/2005-31/5/2006

thanks in advance|||u can you crystal report inbuild function INLAST365DAYS to take date range of 1 complete year

Date Format settings problem

We have set of (200 over) reports with date-formats selected as default date-format as in windows format - dd/MM/yyyy.

Reports are viewed through the viewer (CR version 9) on IE.

We install CR on a different machine which had a date format of MM/dd/yyyy and then realised after looking into the application that the reports were displaying the formats as what is in the control panel settings.

Though we change the settings and the reports when viewed through the designor is correctly displaying the format as expected but that does not seem to be the case when viewed through the viewer on IE.

But once you open one of the reports force a change and save back the settings for each date-columns it shows correctly even in the viewer.

We uninstalled CR (deleted registry entries in hkey_local_machine\software\ and deleted relevant folders) and re-installed back CR only to see no change.

Any ideas what this would be causing this problem?
Any help appreciated !

Regards,
Kaushik.Here is the solution
Create one formula in each report as 'DateFormat'
Edit formula write there 1

Now

Steps
1) Select DateField in your report - > Right Click -> CustomeStyile -> Customise -> Goto Order Frame click formula there - > insert your 'DateFormat' formula there.

Save the report.
Now it will work

2) Through Program pass formula field values
for DateFormat as 1 or 2 or 3

You will get Date in respective format.
Still you have problem ask me.

Thanks
Vilas