need to set date to first of the next month

[sonny1974]

im using sql server and crystal reports and crystal enterprise

i want to put in my where the begin date to be the first of every month.
for instance i have set this report to run may 29th, but i want the begin start date to be june 1,

then next month i want the begin start datre to be july 1.

how do i set it up in my where , that i dont have to change the date every month since this report will be scheduled on crystal enterprise

 

[AlexCuse]

Have a look here:

select dateadd(month, datediff(month, 0, getdate()) + 1, 0)

What you do is start with date 0 (1900-01-01 00:00:00.000
), and then add a number of months (determined by getting the difference in months between current date and 0, and adding 1)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sonny1974]

would this change every month for the next month . since this will be automated on crystal enterprise. also can you give me an example if the report would be for june

 

[sonny1974]

so right now for this month it ran on april 26th for may 1 report

"BI_DTL"."BEN_BGN_DT">={ts '2007-05-01 00:00:00'}

i would like the month to change every month without me manually doing it

 

[AlexCuse]

Yes, the code I posted keys off of the current date, so you would not have to change it.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sonny1974]

how would i set it up, just the way u wrote it?

"BI_DTL"."BEN_BGN_DT">= dateadd(month, datediff(month, 0, getdate()) + 1, 0)

like that? im sorry for being so slow

 

[sonny1974]

also this report will run for mid month also,

so i will have two reports , one for monthend and mid month

so for instance one report will be but with your forula so i dont have to change them manullay

both reports will be run a few days before the dates

so the 5/1/07 report was ran on 4/26/07 and the 5/15/07 will be run on the 5/13/07


BI_DTL"."BEN_BGN_DT">={ts '2007-05-15 00:00:00'}

BI_DTL"."BEN_BGN_DT">={ts '2007-05-01 00:00:00'}

 

[sonny1974]

when i did that i didnt get any results

 

[sonny1974]

it looks like i got the month report to work

how would i set up the other report to look at mid month

 

[AlexCuse]

So do you want it to always run on the fifteenth? If so, here is the code for the next fifteenth:

[COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], 14,
[COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]month[/color], [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]month[/color], 0, [COLOR=#FF00FF]getdate[/color]()), 0)
)

To get the first of the current month, use the code that I already gave you but remove the '+ 1'.

Notice that it uses the same technique you already have (it just does not add 1, because you are not trying to get the fifteenth of the next month). This is a useful one to learn.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom