You must Sign In to post a response.
  • Category: ASP.NET

    Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF)?

    I need to write an SQL Function (UDF) to find the First Day (int) and the Last Day (int) when I specify a date.

    For example: When I specify (20130217) (i.e. Feb 17th, 2013), I should be able to find the first Day (01) and the Last Day (28) for the specified month (in this case, Feb).

    How can I do that? Please provide the necessary code to implement this feature in my SQL Function.

    Thanks.
  • #706565
    Try this query

    DECLARE @mydate DATETIME
    SELECT @mydate = Convert(datetime,'20130217')
    Print @mydate
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
    'First Day of Current Month' AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
    'Last Day of Current Month'


    if you want to UDF than simply put above query in UDF with on input parameter

    Regards & thanks
    Arvind kumar
    Visit--blog.akumars.esoftera.in

  • #706574
    Hai Mohammad Fahhad,
    You can create a function like below which will take the input as date and then based on the date, it will calculate the first day of the month:

    CREATE FUNCTION dbo.GetFirstDayOfMonth(@InputDate DateTime)
    RETURNS TABLE
    AS
    RETURN(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@InputDate)-1),@InputDate),106) AS Date_Value,'First Day of Month' AS Date_Type);
    GO

    To get the last day of the month, you can use the below function:

    CREATE FUNCTION dbo.GetLastDayOfMonth(@InputDate DateTime)
    RETURNS TABLE
    AS
    RETURN(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@InputDate))),DATEADD(mm,1,@InputDate)),106),'Last Day of Month');
    GO

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #706579
    Hi Pawan,

    I want to discuss a few things about my project with you. Can you please add me on my GTalk:

    fahhad.sisgreen@gmail.com

    Thanks.

  • #706586
    hi,

    try this one

    select [firstDay of moth] as text , convert(varchar,dateadd(d,-(day(getdate()-1))getdate()),106) 'Date'

    union all

    select [lastday of month],convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),106)

  • #706590
    Hi,

    I want to discuss a few things about my project with you. Can you please add me on my GTalk:

    fahhad.sisgreen@gmail.com

    Thanks.

  • #706603
    Hi,

    SElect DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE), 0)
    ,DATEADD(MONTH, DATEDIFF(MONTH, -1, @DATE), -1)


    DARE TO BE DIFFERENT

  • #706625
    Hi.,

    Try this,



    DECLARE @mydate DATETIME

    SELECT @mydate = '20130217'

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
    'First Day' AS Date_Type

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
    'Last Day'



    Regards,
    Gandhi


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.