Post

1 follower Follow
0
Avatar

Creating a Timestamp column from a SQL query when the date and time is in seperate columns

You might need to integrate SQL data as a measure into Flow where the timestamp might not be in one single column, see the dataset below: 

To combine the date and time field, we can add them together. The trick is to make sure they are of the same datatype. So we need to cast them to be of the datetime data type although they are of type date and time separately, see below:

cast(Date as datetime) + cast(Time as datetime) as Timestamp

I also  made sure that the resulting Colum is called Timestamp. 

So the resulting  query would look as follow ( see the result from SQL below):

Select RollerTemp1 as Value, 192 as Quality , cast(Date as datetime) + cast(Time as datetime) as Timestamp from Inker where InkerID = 'C'

The only thing left is to add the time period placeholders for the Flow engine to replace:

Select RollerTemp1 as Value, 192 as Quality , (cast(Date as datetime) + cast(Time as datetime)) as Timestamp from Inker where InkerID = 'C'

and  (cast(Date as datetime) + cast(Time as datetime)) > [PeriodStart] and  (cast(Date as datetime) + cast(Time as datetime)) <= [PeriodEnd]

Flow Support

Please sign in to leave a comment.