Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I want to convert sql script into qliksense.
but below function is date function in scql.Which date frunction I shoul duse to convert it into qliksense.
SQL: SWITCHOFFSET(g.CreatedDate, '+05:30')
Please help me to understand this.
Thanks,
Rupali M
#Scripting
#SQL To Qlik
Hi @rupaliqlik,
Assuming your CreatedDate is in a datetime format you can use this to add 5:30 to the field.
timestamp(TIMESTAMP#(CreatedDate, 'DD/MM/YYYY hh:mm:ss TT') + time('5:30')) as NewCreatedDate,
If CreatedDate is only a date without the time already populated you can add:
timestamp(CreatedDate + time('5:30')) as NewCreatedDate,
Depending on your use case you may just need to use the converttolocaltime() function.
Regards
Anthony
HI,
@Anthonyj I am getting below result When I am using timestamp(CreatedDate + time('5:30')) as NewCreatedDate.
Expected result is 2022-03-02 20:00:01.777 +05:30
BTW timestamp(TIMESTAMP#(CreatedDate, 'DD/MM/YYYY hh:mm:ss TT') + time('5:30')) as this is not working
Thanks,
Rupali Ethape
Hi Rupali,
Your timestamp is in a different format than my assumption but it looks like your set variables are taking care of this for you.
timestamp(TIMESTAMP#(date, 'YYYY-MM-DD hh:mm:ss.ffffff') + time('5:30'))
So, it looks like the function is performing the task of adding 5:30 to the output but you're looking for an output that shows the original created time but include the text of +5:30 on the end.
If you're looking to add the text to the end of the create date then you can add the field CreatedDate & ' +5:30' to your dimension. This will give the result of 2022-03-02 20:00:01.777 +05:30 in your table. You can also add it in the load script which will create a text type column.
Apologies if I've misunderstood your requirement.
Thanks
Anthony