Convert date/time to BST (British Summer Time)

[Sach2022]

Hi, 
I have a flow which calls a Stored Procedure from an Azure SQL Database.  All Databases are in UTC format.

I need to convert the date/time fields to BST (British Summer Time) - clocks changed to +1hr end of March 2022.

I create a CSV Table and send it via email as part of my flow.

Please can someone help please?  I assume I need a convert syntax of some sort to wrap around the DB object?

Thanks.

[Sach2022]

As a quick update, this is what I use as an Expression Value for one of my date/time fields (to handle NULLs):

if(empty(item()?['Arrived']), '-', formatDateTime(item()?['Arrived'], 'dd/MM/yyyy HH:mm'))

I need something extra to handle BST and also Winter Time when the clocks change again in October.

Thanks!

[RobElliott]

@Sach2022 use the convert time zone action to specify your base timezone, timezone to convert to and the date format - for UK dates use the custom value and enter dd/MM/yyyy

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Sach2022]

Hi @RobElliott - thanks for your response.

Here is my flow:

Where do I actually insert the Convert time zone step?  And how do i configure it?

My CSV has hh:mm:ss and I also have another Flow with dd/MM/yyyy hh:mm:ss:

Need help with this please i.e.

 1. Where to put the Convert Time Zone step
 2. How to configure the Convert Time Zone step

Thanks!

[RobElliott]

@Sach2022 your screenshot of the convert time zone action shows you've got it wrong. The base time is the date you want to convert, so that's your column in the spreadsheet which you select from the dynamic content box. The source tie zone is the current time zone the date is in. The destination time zone is the timezone you want the date to be inb, so select one 1 hour ahead of UK. The format string is dd/MM/yyyy.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Sach2022]

ok, I think I've got this sussed out - but I have two issues.  
  1. It creates a step 'For Each' meaning it iterates through each record and converts from UTC to UTC+00:00:00 (GMT) - this is an overhead, i.e. it takes 54 secs+++

  2. I have NULL values in my dates/times, so it's failing. I therefore need a try catch statement, i.e. if NOT NULL, do the conversion, otherwise do nothing.

Help please!  Thanks.

[Sach2022]

Hi all - can someone please help?  

[RobElliott]

@Sach2022 sorry, I don't quite understand, help with what? I've given you the solution but if your scenario requires an apply to each then yes it can take a minute or two to loop through each item, particuarly if you have a large list. That's entirely normal. I have flows that can last for 10-15 minutes because of the apply to each loop.

If you could post a screenshot of your flow as it is now we can see why there might be the apply to each.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Sach2022]

Hi @RobElliott - no problem.
Here it is.  It's failing because my objects may have NULL values, more so the second and third items which are 'Time In' and 'Time Out'. Basically when I mouse over the red items it says: items('Apply_to_each')?['Time In'] 

I need to put a if..then expression in so that if it's NULL, ignore, if it's not NULL then do the conversion.

Help!  Thanks in advance.

[Sach2022]

For awareness, I've used this (as an example):

if(empty(items('Apply_to_each')?['Time In']), '', items('Apply_to_each')?['Time In'] )  ?

It worked (a bit) then fell over.

I get on the Create CSV table bit (that's nested below the Convert time zone x blocks): BadRequest. The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'.

Help please!  Thank you.

[Sach2022]

I've narrowed it down to my Convert time zone 3 block, it has this error: InvalidTemplate. Unable to process template language expressions for action 'Convert_time_zone_3' at line '0' and column '0': 'In function 'converttimezone', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

[Sach2022]

Quick update...

I think my syntax was at fault. I used a 0 instead of '' (empty string, see below) and now it's working, but another issue:

if(empty(items('Apply_to_each')?['Time Out']), 0, items('Apply_to_each')?['Time Out'])

I am using this for my CSV - I thought of expanding out the objects, but not working for some reason:

Now I can't see how to attach my CSV from the Create CSV Table in my Send Mail (should be a purple CSV icon object but it's gone!):

Only the Current Item blue icon is there.

Help!  I'm one step forward from before...

[Sach2022 · Accepted solution]

I've sorted this myself, using an expression for the objects in CSV: if(empty(item()?['Time Out']), '-', convertTimeZone(item()?['Time Out'],'UTC','GMT Standard Time','HH:mm:ss'))