Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
Use ISOWW:
I would also format the date as 'YYYY-MM-DD' since this is the MS SQL standard date format.
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
You need to write a case statement, I don't think there is ISOYY in MS SQL. Here is the statement:
This may also be a solution if you don't want to use a case statement, but I would definitely use a case statement:
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