Customizing format of day from datepart function


(Jayant Singh) #1

Hello colleagues,
As documented, the datepart() function helps us to retrieve different parts of the date; month, day or year. Our question is specific to extracting day, using datepart(date,'DAY') function.

When the date is suppose April 4, 2019 we get the day as 4, single digit number. And when date is April 24, 2019, we get the day as 24, expected.
Is it possible to obtain 2 digit day without using any sort of manual concatnation?

Example,datepart(2019-04-4,'DAY') shd give 04 rather than 4.
Advice/help is greatly appreciated.


(Maksim Zhelyazkov) #2

Dear Jayant,

Unfortunately, the datepart() function returns an integer, which cannot be modified. Thus, you are not able to get a double digit number for single digit days of the month.
Depending on your use case scenario you can modify the result at a later stage in your DQC plan to achieve the desired result.

Kind regards,
Maksim


(Danny Ryan) #3

Hi Jayant,

You could try the toString function to return a string value ‘04’ or ‘24’ as per the example input dates you mentioned.

toString(
toDate('2019-04-04','yyyy-MM-dd')
,'dd')

Would return ‘04’

toString(
toDate('2019-04-24','yyyy-MM-dd')
,'dd')

Would return ‘24’

Let us know how you get on.

Kind Regards
Danny
Accenture / Telefonica O2 UK Ltd.


(Jayant Singh) #4

Thank you for your suggestion. I will make sure to give it a shot.