Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stevegimbrollmt
Creator II
Creator II

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
Creator II
Creator II
Author

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

View solution in original post

8 Replies
antoniotiman
Master III
Master III

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
Specialist III
Specialist III

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
Creator II
Creator II
Author

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
Specialist III
Specialist III

Use ISOWW:

Capture.PNG

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

stevegimbrollmt
Creator II
Creator II
Author

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
Specialist III
Specialist III

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

Capture.PNG

sinanozdemir
Specialist III
Specialist III

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
Creator II
Creator II
Author

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