User Defined Function in Serverless SQL Pool

Question

Dear Team,

We are using Synapse server less SQL Pool.

We have few views which got date field with UTC format and this view is the source data in Power BI. Currently, we are converting it using OFFSET but it's taking so much of time.

Example Date value:
2021-07-03T04:04:53.219Z

SQL Code Example:
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIME2, createdDate, 120) , DATEPART(TZOFFSET, CONVERT(DATETIME2,createdDate, 120) AT TIME ZONE 'Arabian Standard Time'))) AS Created_Date

Could you please suggest me if it's possible to create user defined function in Synapse Server less SQL Pool? If so, can you please share the sample for converting from UTC to Arabian Standard Time?

Thanks,
P.K.Divakar

Answer 1

Hello @,
Thanks for the ask and using Microsoft Q&A platform .
Since your ask is on the serverless pool and at this time the serverless pool does not support scalar valued function , but only table values function .

.

Request you to please log this as a feature here

Product group does monitor the request and they can plan for the implementation in future .Once you log the feature request you will also be notified on the status of the request . Please to share the link of the ask here , so that other community members can benefit from that .

Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

@HimanshuSinha-msft .

Thank you, noted.

I have many date fields where I need to convert it from UTC to Arabian standard time. I am using the below SQL code to convert the date.

SQL Code Example:
SELECT
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIME2, createdDate, 120) , DATEPART(TZOFFSET, CONVERT(DATETIME2,createdDate, 120) AT TIME ZONE 'Arabian Standard Time'))) AS Created_Date
FROM
OPENROWSET(
BULK '*******/Time Attendance/Raw_TimeAttendance_2022-01-04.json',
FORMAT = 'CSV',
FIELDQUOTE = '0x0b',
FIELDTERMINATOR ='0x0b'
)
WITH (
jsonContent varchar(MAX)
) AS [result]
)MAIN

Can we able to do this same using table values function? If so, can you share the sample code for it please?

Thanks,
Divakar