Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

stevegimbrollmt
Not applicable

Qlik Weekname function equivalent in tsql

Hi,

Wanted to check, you know when you use

weekname('12/01/2013')Returns 2013/02.

Does anyone know in Mssql what I can use to return same result please

Thanks

1 Solution

Accepted Solutions
stevegimbrollmt
Not applicable

Re: Qlik Weekname function equivalent in tsql

Hi,

I have found a way to do it and its this way

CASE

         WHEN Datepart(isowk,  a.time)=1

             AND Month( a.time)=12 THEN Year( a.time)+1

         WHEN Datepart(isowk,  a.time)=53

             AND Month( a.time)=1 THEN Year( a.time)-1

         WHEN Datepart(isowk,  a.time)=52

             AND Month( a.time)=1 THEN Year( a.time)-1           

         ELSE Year( a.time)

END as ISO_Year

Thanks for your help

8 Replies
antoniotiman
Not applicable

Re: Qlik Weekname function equivalent in tsql

Hi Steven,

may be

SELECT Cast(DATEPART(yy,'2013-01-12') as Char(4))

+'/'+Right('00'+Cast(DATEPART(ww, '2013-01-12') as VarChar(2)),2)   -> 2013/02

Regards,

Antonio

sinanozdemir
Not applicable

Re: Qlik Weekname function equivalent in tsql

Hi,

You can use either datepart() or datename() functions:

Capture.PNG

Make sure that you convert both year and week day parts to varchar in order to concatenate by using plus sign.

stevegimbrollmt
Not applicable

Re: Qlik Weekname function equivalent in tsql

Thanks

What I am trying to get is

select cast(datepart(yyyy,'2016/01/01') AS varchar) + '/' + cast(datepart(ISO_WEEK,'2016/01/01') as varchar)

I would want to expect 2015/53 as 01/01/2016, like the WeekName would give as a year.

Seems like its not really possible. could not find anything like that

sinanozdemir
Not applicable

Re: Qlik Weekname function equivalent in tsql

Use ISOWW:

Capture.PNG

I would also format the date as 'YYYY-MM-DD' since this is the MS SQL standard date format.

stevegimbrollmt
Not applicable

Re: Qlik Weekname function equivalent in tsql

Agreed but in reality the year should be 2015 for that day, if you see the calendar, you would see that that day is still within the 2015 /53 and not 2016/53

That is what im trying to get. that 2015

thanks

sinanozdemir
Not applicable

Re: Qlik Weekname function equivalent in tsql

You need to write a case statement, I don't think there is ISOYY in MS SQL. Here is the statement:

Capture.PNG

sinanozdemir
Not applicable

Re: Qlik Weekname function equivalent in tsql

This may also be a solution if you don't want to use a case statement, but I would definitely use a case statement:

Capture.PNG

stevegimbrollmt
Not applicable

Re: Qlik Weekname function equivalent in tsql

Hi,

I have found a way to do it and its this way

CASE

         WHEN Datepart(isowk,  a.time)=1

             AND Month( a.time)=12 THEN Year( a.time)+1

         WHEN Datepart(isowk,  a.time)=53

             AND Month( a.time)=1 THEN Year( a.time)-1

         WHEN Datepart(isowk,  a.time)=52

             AND Month( a.time)=1 THEN Year( a.time)-1           

         ELSE Year( a.time)

END as ISO_Year

Thanks for your help