Query to get Month , Day and Week number of current month.

Question

Hi,

Need a query to get Month , Day and Week number of current month.

 

Sample table

 

Date	Month Name	Day	Week	Year
6/29/2024	Jun	Sat	WK5	2024

Answer 1

I used case statements for day and month instead format parse to give full control of names:

select d as Date, 
    case month(d)
        when 1 then 'Jan'
        when 2 then 'Feb'
        when 3 then 'Mar'
        when 4 then 'Apr'
        when 5 then 'May'
        when 6 then 'Jun'
        when 7 then 'Jul'
        when 8 then 'Aug'
        when 9 then 'Sep'
        when 10 then 'Oct'
        when 11 then 'Nov'
        when 12 then 'Dec'
    end "Month Name",
    case month(d)
        when 1 then 'Jan'
        when 2 then 'Feb'
        when 3 then 'Mar'
        when 4 then 'Apr'
        when 5 then 'May'
        when 6 then 'Jun'
    end "Month Name",
    case datepart(dw, d)
        when 1 then 'Sun'
        when 2 then 'Mon'
        when 3 then 'Tue'
        when 4 then 'Wed'
        when 5 then 'Thu'
        when 6 then 'Fri'
        when 7 then 'Sat'
    end Day,
    'WK' + cast(datepart(wk,d) - datepart(wk, dateadd(mm, datediff(m,0,d),0)) + 1 as varchar(1)) "Week",
    year(d) "Year"
from (select cast('6/29/2024' as date) d) as t

Answer 2

SELECT left(datename(MONTH, sysdatetime()), 3), left(datename(WEEKDAY, sysdatetime()), 3), 
        concat('WK', datepart(ISO_WEEK, sysdatetime())), year(sysdatetime())