Datetime syntax from SQL to CR

[Former Member]

Hello,

I have the following date format in SQL.

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2011')+3,0))

When I ran it in SQL, the result is:

2011-03-31 23:59:59.000 It returns the year (2011), the month (03), last day of the month (31), and time (23:59:59.000).

How we can do it in CR?

Thanks.

-Bill

[DellSC · Accepted solution]

If you're just joining tables in your report, I would create a SQL Expression that does this - the syntax will be essentially the same.

If you're using a command or a stored procedure instead of joining tables, then you add this as a "field" in the select statement.

If you insist on doing this in Crystal, you might be able to do something like this (Assumes you have the year in a parameter, and you know the number of months you want):

DateAdd('s', -1, Date(2011, 4, 1))

-Dell

[Former Member · Accepted solution]

Thanks Dell.

It works fine except I am not able to get the month of December.

[Former Member · Accepted solution]

I am doing it for CR.

[Former Member · Accepted solution]

Now I got it. Thanks again.

[DellSC · Accepted solution]

Try this instead - it will work from the first day for each month:

DateAdd('s', -1, DateAdd('m', <month number + 1>, Date({?Year}, 1, 1)))

-Dell

[Former Member · Accepted solution]

I like your second formula. Thanks so much.