Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I Have the following scenario.
If customer place the order on Saturday or Sunday, the OrderDateTime should show as The Monday's Date and the time should show as '00:00:00'.
For eg: If the order was placed on 08-10-2014(Sunday), The OrderDateTime should show as 08-11-2014 00:00:00
Any idea?
Marco,
A simpler method to advance the time may be WeekStart():
If(WeekDay(OrderDateTime)>=5
,timestamp(WeekStart(OrderDateTime,1))
,OrderDateTime
) as OrderDateTimeAdjusted
-Rob
Load
TimeStamp(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) as OriginalOrderDate,
TimeStamp(IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sat',
TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+2),
IF(WeekDay(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')) = 'Sun',
TimeStamp(Date(Floor(Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss')))+1,),Timestamp#(OrderDate,'DD-MM-YYYY hh:mm:ss'))),'DD-MM-YYYY hh:mm:ss') as NewOrderDate
Inline
[
OrderDate
08-08-2014 10:10:00
09-08-2014 10:10:00
10-08-2014 10:10:00
11-08-2014 10:10:00
];
Pardeep : Change Dates as per your system or database data.. Looks like your Dates are MM-DD-YYYY... while in my above example, it is DD-MM-YYYY... so change accordingly...
This is something called as Incredible Stuff. Just loved it.
Good Job.
Regards
Aviral Nag
Hi,
another solution might be:
If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime)
LOAD *,
WeekDay(OrderDateTime) as WeekDayOrderDateTime,
WeekDay(OrderDateTimeAdjusted) as WeekDayOrderDateTimeAdjusted;
LOAD *,
If(WeekDay(OrderDateTime)>=5, Timestamp(Floor(OrderDateTime+7-WeekDay(OrderDateTime)), 'MM-DD-YYYY hh:mm:ss'), OrderDateTime) as OrderDateTimeAdjusted;
LOAD Timestamp(Timestamp#('01/01/2014', 'MM/DD/YYYY')+250*Rand(), 'MM-DD-YYYY hh:mm:ss') as OrderDateTime
AutoGenerate 100;
hope this helps
regards
Marco
Marco,
A simpler method to advance the time may be WeekStart():
If(WeekDay(OrderDateTime)>=5
,timestamp(WeekStart(OrderDateTime,1))
,OrderDateTime
) as OrderDateTimeAdjusted
-Rob
Very nice, I didn't remember this function.
Thanks Rob
Regards
Marco
Thanks Rob
Its Working