topic Re: Qlik Weekname function equivalent in tsql in QlikView

Qlik Weekname function equivalent in tsql

stevegimbrollmt — Mon, 07 Nov 2016 14:49:00 GMT

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

Re: Qlik Weekname function equivalent in tsql

antoniotiman — Mon, 07 Nov 2016 15:31:55 GMT

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

Re: Qlik Weekname function equivalent in tsql

sinanozdemir — Mon, 07 Nov 2016 15:40:14 GMT

Hi,

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

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

Re: Qlik Weekname function equivalent in tsql

stevegimbrollmt — Mon, 07 Nov 2016 15:58:47 GMT

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

Re: Qlik Weekname function equivalent in tsql

sinanozdemir — Mon, 07 Nov 2016 16:04:44 GMT

Use ISOWW:

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

Re: Qlik Weekname function equivalent in tsql

stevegimbrollmt — Mon, 07 Nov 2016 16:09:56 GMT

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

Re: Qlik Weekname function equivalent in tsql

sinanozdemir — Mon, 07 Nov 2016 16:12:56 GMT

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

Re: Qlik Weekname function equivalent in tsql

sinanozdemir — Mon, 07 Nov 2016 16:18:50 GMT

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

Re: Qlik Weekname function equivalent in tsql

stevegimbrollmt — Tue, 08 Nov 2016 11:31:22 GMT

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