Solved

MYSQL - How to get 1st, 2nd, 3rd etc...  Monday, Tuesday, Wednesda, etc ... date in a month

Posted on 2007-11-21
10
1,738 Views
Last Modified: 2012-08-13
Having trouble finding the 1st, 2nd, whatever day in a month.
I know the month and year. Say...  Sept. 2008
I want to know what is the Date of First Monday in that month. or Second Friday, etc.
I have seen some other functions in pSQL and Oracle but not MySQL. I have MySQL version 5.x
0
Comment
Question by:Bob-Villa
10 Comments
 
LVL 7

Expert Comment

by:dansoto
ID: 20330907
Do you want to find a particular record for the first day ?
0
 
LVL 1

Author Comment

by:Bob-Villa
ID: 20330947
i want to know the date of the first monday in september of 2008 returned in MYSQL format. not the date of an actual record.
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 20331379
Hey Bob-Villa:
Since I do not have access to MYSql database, below is a function that I created in SQL Server 2000 (TSQL) which does exactly what you asked for. However, there are couple of this things you need to know here:

1. The function below could cross over to next month if you pass high value to the @DayNum parameter.
2. Because it is coded in TSQL, you might have to change some syntax and function names to make it compatible to MySQL.

Here is an example on how to call this function in TSQL. Say for example you want to find out the first monday in September 2008:



FUNCTION CALL

Declare @Val Datetime
Select @Val =  pkamal.sp_GetWeekdayDate ('2008-09-01','MONDAY',1)
Print @val

Where @Date is always the first day of the Month in this September 01, 2008 ('2008-09-01'),
            @DayName is the name of the weekday you want to search which is this case in Monday, and
           @DayNum is the 1st, 2nd, 3rd etc @DayName('MOnday' in this case) if the select month.


FUNTION OUTOUT
This is th output by the function when the I ran it with same example as above:     Sep  1 2008 12:00AM



FUNCTION CODE:

CREATE FUNCTION sp_GetWeekdayDate (      @Date      Datetime,
                        @DayName       varchar(10),
                        @DayNum       Tinyint
                                                                 )
            RETURNS Datetime
AS

BEGIN

DECLARE @InitialDate      Datetime,
                @ReturnDate      Datetime

SET @InitialDate =CASE WHEN DATENAME(DW, @Date)= @DayName THEN @Date
                              WHEN DATENAME(DW, DATEADD(d,1,@Date))= @DayName THEN DATEADD(d,1,@Date)
          WHEN DATENAME(DW, DATEADD(d,2,@Date))= @DayName THEN DATEADD(d,2,@Date)
         WHEN DATENAME(DW, DATEADD(d,3,@Date))= @DayName THEN DATEADD(d,3,@Date)
         WHEN DATENAME(DW, DATEADD(d,4,@Date))= @DayName THEN DATEADD(d,4,@Date)
        WHEN DATENAME(DW, DATEADD(d,5,@Date))= @DayName THEN DATEADD(d,5,@Date)
                       WHEN DATENAME(DW, DATEADD(d,6,@Date))= @DayName THEN DATEADD(d,6,@Date)
         ELSE @Date
          END

SET      @ReturnDate = CASE WHEN @DayNum IS NULL or @DayNum In (0,1) Then @InitialDate
                           ELSE Dateadd(d,(7*(@DayNum-1)),@InitialDate)
                  END

RETURN (@ReturnDate)
END


Let me know if you still have issues understanding the logic or if you have issues converting over to MYSql.

Hope this helps.

P.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 52

Expert Comment

by:_agx_
ID: 20331820
(No points please.  This is just a translation)

Here is a rough translation of pssandhu's function into MySQL. Watch out for line wrapping


delimiter //
 

CREATE FUNCTION fn_GetWeekdayDate (      

TheDate  Datetime,

DayName  varchar(10),

DayNum   Tinyint

)

RETURNS Datetime

BEGIN

  DECLARE InitialDate     Datetime;

  DECLARE ReturnDate      Datetime;

  DECLARE Dow int;

    

  SET InitialDate = 

	CASE WHEN Date_Format(TheDate, '%W') = DayName THEN TheDate

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 1 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 1 DAY)

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 2 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 2 DAY)

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 3 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 3 DAY)

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 4 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 4 DAY)

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 5 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 5 DAY)

             WHEN Date_Format(Date_Add(TheDate, INTERVAL 6 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 6 DAY)

             ELSE TheDate

        END;
 

  SET ReturnDate = CASE WHEN DayNum IS NULL or DayNum IN (0,1) Then InitialDate

                        ELSE Date_Add(InitialDate, INTERVAL (7*(DayNum-1)) DAY)

                   END;
 

  /* uncomment this if you want to return null instead 

     of allowing crossover months
 

  SET ReturnDate = CASE WHEN Month(TheDate) = Month(ReturnDate) THEN ReturnDate 

   			ELSE NULL 

		   END;

  */
 

  RETURN ReturnDate;

END

//
 

delimiter ;
 

SELECT fn_GetWeekdayDate('2008-09-01', 'MONDAY', 1);

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 20479810
What was inadequate about pssandhu's suggestion? IMO it did exactly what the asker requested.  

I don't want any points for it, but did they try the translated version?  
0
 
LVL 1

Author Comment

by:Bob-Villa
ID: 20501332
pssandhu provided sql code that did not work with mysql. agx provided a rough translation that needed some work. I was looking for some built-in function, or an advanced query that I was unfamiliar with, perhaps I didn't ask clearly. I was able to accomplish the same thing (in the 43 lines above) in 2 lines of perl code. If I were another user searching for the same solution I don't feel the answers given here would have been a great solution. Didn't mean to offend anyone, I was just trying to clean up my 2 open questions. Please don't respond with any additional comments I don't have time to debate this issue. I pay my $10 a month to find quick answers in a pinch, not to debate the quality of the answers given by other members.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20501713
No need for debate.  No further suggestions were offered because no other comments were made until now.  For future reference if a suggestion doesn't meet your needs, for whatever the reason, feel free to ask for alternatives :)  In addition, be sure to provide complete details about your environment so responders will know what options are available to you.



0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MYSQL Record Number 3 54
PhpMyAdmin, mysql database table editing 4 44
First name pregmatch 11 30
Clean text to insert in database 9 41
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now