Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Interview Questions

Question of the Week -08/04/2014

Byvivekjohari

Apr 8, 2014 #Question of the Week


Suppose we need to find out the name of all the months in a given year (Year will be given as an input parameter) where first day of the month falls on weekend (Saturday or Sunday). 

So what should be the query/stored procedure to get the above result?

DMCA.com

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Interview Questions SQL Advanced SQL Indexes

Interview question – Is Clustered index on column with duplicate values possible?

Aug 13, 2015 vivekjohari
Interview Questions

Question of the Month (July)

Aug 3, 2015 vivekjohari
Interview Questions SQL Basic SQL Server

Script to find complete months between two given dates

Jul 10, 2015 vivekjohari
30 thought on “Question of the Week -08/04/2014”

  1. create proc techmixing_8_4_14 @yr as numeric
    as
    SET NOCOUNT ON;
    set datefirst 1 — to set first day as monday

    declare @dt as varchar(8);
    declare @count as numeric=1
    create table #t1 (mn varchar(20)) — Temporary table
    while @count <=12
    begin

    set @dt=convert(varchar(4),@yr ) + isnull(REPLICATE('0',2-len(convert(varchar(2),@count))),'')+convert(varchar(2),@count)+ '01'

    set @count=@count+1
    if datepart(dw,convert(date,@dt,112 )) >=6
    begin
    insert into #t1 (mn) select datepart(MM,@dt)
    end

    end
    select * from #t1

    go

    exec techmixing_8_4_14 2012

    Reply

  2. Just to little modify the Bharat answer by adding "No count off" in the end.

    Create proc techmixing_8_4_14 @yr as numeric
    as
    begin
    SET NOCOUNT ON;
    set datefirst 1 — to set first day as monday

    declare @dt as varchar(8);
    declare @count as numeric=1
    create table #t1 (mn varchar(20)) — Temporary table
    while @count <=12
    begin

    set @dt=convert(varchar(4),@yr ) + isnull(REPLICATE('0',2-len(convert(varchar(2),@count))),'')+convert(varchar(2),@count)+ '01'

    set @count=@count+1
    if datepart(dw,convert(date,@dt,112 )) >=6
    begin
    insert into #t1 (mn) select datename(MM,@dt)
    end

    end
    select * from #t1
    set nocount off
    end
    go

    exec techmixing_8_4_14 2012

    Reply

  3. Declare @Months Varchar(60)

    Declare @loop int
    SET @loop=1

    WHILE @loop<=12
    BEGIN
    IF(Datename(dw,'2014/'+str(@loop)+'/1') in ('Saturday','Sunday'))
    BEGIN
    SET @Months=COALESCE(@Months+ ', ', '')+ DateName( month , DateAdd( month , @loop , 0 ) -1)
    END
    SET @loop=@loop+1
    END

    Print @Months

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading