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))]
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
 
wkrasnerConnect With a Mentor 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))
0
 
Carla RomereDirector of Information TechnologyAuthor 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.
0
 
wkrasnerCommented:
{tblWorkOrder.dtCompleted} in  DateTime(DATEPART("YYYY",CurrentDate), DATEPART("m",CurrentDate)-1, 1,6,0,0)
TO   DateTime(CurrentDate & Time(06,00,00))
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Carla RomereDirector of Information TechnologyAuthor Commented:
Stupendous! I was trying to make it more difficult than it needed to be.
0
 
mlmccCommented:
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
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Duly noted - I modified the formula. Thanks for the heads up!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.