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

mrrobottelg 20 Reputation points
2024-06-29T10:28:08.37+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,323 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 61,186 Reputation points
    2024-06-29T19:09:55.21+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2024-06-29T21:58:38.87+00:00
    SELECT left(datename(MONTH, sysdatetime()), 3), left(datename(WEEKDAY, sysdatetime()), 3), 
            concat('WK', datepart(ISO_WEEK, sysdatetime())), year(sysdatetime())
    
    0 comments No comments