Convert date/time to BST (British Summer Time)

(0) Share

Report

Posted on by 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.

Categories:

Using flows

I have the same question (0)

All responses (12)

Answers (1)

Sach2022 56 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

Hi all - can someone please help?

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Sach2022 56 on at

Like (0)

Report

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.'.

Was this reply helpful? Yes No