asked on 9/6/2011

Building specific dates and times

I am trying to build a date time for the first of last month at 6am through the first day of this month at 6am. This is the formula I've built, but I'm getting an error on the first one saying that where I have 01 & Time(06,00,00) there should "be a number here".

This formula works for getting 6am 8 days ago and 6am 1 day ago.
{VW_BATCH_DETAILS_ALL.DATETIME_BATCH}
IN[DateTime(CurrentDate-8 & Time(06,00,00)) to DateTime(CurrentDate-1 & Time(06,00,00))]

However, this formula (adapted from that one) does not work.
{VW_BATCH_DETAILS_ALL.DATETIME_BATCH}
IN [DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate)-1, 01 & Time(06,00,00))
TO DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate), DATEPART("dd",CurrentDate)-1 & Time(06,00,00))]

Crystal Reports

Last Comment

Carla Romere

9/6/2011

Carla Romere

9/6/2011

ASKER

I modified that formula on the "TO" date as shown:
{VW_BATCH_DETAILS_ALL.DATETIME_BATCH}
IN [DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate)-1, 01 & Time(06,00,00))
TO DateTime(CurrentDate & Time(06,00,00))]

However, I still get the error on the "FROM" datetime. This report will run automatically on the first of the month sometime after 6am.

Will

9/6/2011

{tblWorkOrder.dtCompleted} in DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate)-1, 1,6,0,0)
TO DateTime(CurrentDate & Time(06,00,00))

ASKER CERTIFIED SOLUTION

Will

9/6/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Carla Romere

9/6/2011

ASKER

Stupendous! I was trying to make it more difficult than it needed to be.

Mike McCracken

9/6/2011

Your errror is because DateTime uses
DateTime(DatePart,TimePart)
or
DateTime(yyyy,mm,dd,hh,mm,ss)

There is a problem with the chosen formula. It won't work for Jaunary since you aren't decreasing the year and month 0 doesn't exist. Also the end time is today at 6am not the first of the month at 6am.

Actually you need to use

{VW_BATCH_DETAILS_ALL.DATETIME_BATCH} in DateTime(Minimum(LastFullMonth),Time(6,0,0)) to DateTime(Date(Year(CurrentDate),Month(CurrentDate),1),Time(06,00,00))

mlmcc

Carla Romere

9/6/2011

ASKER

Duly noted - I modified the formula. Thanks for the heads up!