Generate the Date Rows with Time

Hi Team,

my Input format is DateTime Type I want to Generate the rows based on the difference between the StartTime and EndTime dates.

Input:

StartTime EndTime

2019-07-22 04:33:55 2019-07-25 05:25:42

Required Output Format:

StartTime EndTime

2019-07-22 04:33:55 2019-07-22 23:59:59

2019-07-23 00:00:00 2019-07-23 23:59:59

2019-07-24 00:00:00 2019-07-24 23:59:59

2019-07-25 00:00:00 2019-07-25 05:25:42

2. How to find out time difference between StartTime and EndTime (in seconds)?

Thank you

Tips and Tricks

Date Time

Accepted answers

RolandSchubert

Hi @dondapati ,

the list of dates can be created using the GenerateRows tool, to calculate the difference you can use DateTimeDiff function. See attached workflow.

Best regards

Roland

DateTimeRange.yxmd

All comments

philipmannering

You could use the attached to get what you need.

workflow

CalcTimeDiff.yxmd

DiganP

@dondapati I would use the generate rows tool with a loop where the initialization expression is your [start] and the condition is [date]<=[End] with the loop expression DateTimeAdd([date],1,"days").

You can then use the formula tool to find the difference between end and start in seconds, datetimediff([End],[Start],'seconds')

Attached is the workflow!

datetimeworkflow.yxmd

danilang

Hi @dondapati

You can us the following

The key to get the EOD times that you're looking for is to set the initialization value in the Generate Rows tools to

DateTimeAdd(DateTimeAdd(todate([StartTime]),1,"days"),-1,"seconds")

which sets the end time to the last second of the input StartTime. From there, you add a day until your date is larger than the EndTime + 1. The final formula tool modifies the startime for rows>1 and the last Endtime, resulting in

Dan

DateTimes.yxmd

RolandSchubert

Hi @dondapati ,

the list of dates can be created using the GenerateRows tool, to calculate the difference you can use DateTimeDiff function. See attached workflow.

Best regards

Roland

DateTimeRange.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703