Building specific dates and times

Carla Romere
Carla Romere used Ask the Experts™
on
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))]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Carla RomereDirector of Information Technology

Author

Commented:
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.
WillOwner

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

{VW_BATCH_DETAILS_ALL.DATETIME_BATCH} in  DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate)-1, 1,6,0,0)
TO   DateTime(CurrentDate & Time(06,00,00))
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Carla RomereDirector of Information Technology

Author

Commented:
Stupendous! I was trying to make it more difficult than it needed to be.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 RomereDirector of Information Technology

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial