SQL SERVER – Query to Find First and Last Day of Current Month

Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
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'
UNION
SELECT
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , This was requested by my co-worker.

About these ads

177 thoughts on “SQL SERVER – Query to Find First and Last Day of Current Month

  1. Your SQL queries for working out dates helped so much. I was on the right track but not quite there

    — Last days of previous months

    Like

    • I have a small query I recently used for quarterly report.
      you can mondify to trunc by month ‘M’.

      select
      sysdate,
      trunc(to_date(Add_months(sysdate,-3)), ‘Q’) “From Date”,
      trunc(sysdate,’Q’)- 1 “to Date”
      from dual
      where EXTRACT(MONTH FROM sysdate) in(1,4,7,10);

      Like

  2. hi,

    I have two tables reciept and issue.
    Reciept have the foloowing details

    partno date rquantity rrate
    a 10/1/2005 50 15
    a 15/4/2005 10 22
    a 11/7/2005 40 20
    b 10/6/2005 30 20
    b 25/6/2005 15 22

    Issue have the foloowing details

    partno date iquantity irate sval lval
    a 14/1/2005 10 10
    a 15/5/2005 20 22
    a 1/8/2005 20 22
    b 11/7/2005 10 20

    what i have to do is
    take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calcucated result in “lVal” and ‘sval’ column
    iquantity(irate)+iquantity(irate-rrate)
    example, first we have to subtract
    10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50
    20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440
    20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440.

    and also if the date difference between dates of two tables are greater than 365 days then they have to stored in the column lval otherwise in the column sval. And this should be repeated to all items.

    Like

  3. Hi
    i am looking for the function which gives last working day of the month for the given time period

    example:it should be like this

    getlastdayofmonth(@start_date,@end_date)

    –so it should give all the dates (last day of month) between start_date and end_date

    really apreciate if anyone can help with this

    thank you

    Like

  4. I have used the code you wrote as follows to find the start of the current and previous months.

    {
    declare @mydate datetime
    select @mydate = getdate()

    SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120) as ‘StartDate’

    SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120) as ‘EndDate’
    }

    However to use these I need them to be in date format with the times represented as 00:00:00:000 irrespective of what time of day the select statements are run.

    Is this possible ?

    Like

    • I ran the following code as part of a statement to capture
      something occuring between the first of the current month and the end of the current month however when ever i refrence Stardate or Enddate i get the following error. Any ideas how to make the columns remain in effect throuhout the entire query?

      Msg 207, Level 16, State 3, Line 10
      Invalid column name ‘Startdate’.
      Msg 207, Level 16, State 3, Line 10
      Invalid column name ‘Enddate’.

      declare @mydate datetime
      select @mydate = getdate()

      SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120) as ‘StartDate’

      SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120) as ‘EndDate’

      Like

    • This is a great reference! Thank you so much!

      I would like to ask how I could return the date value with the time as well.

      For example,

      I want the result to be

      ’12/01/2009 00:00:00′

      What needs to be done to the above code to achieve this?

      Thanks!
      Joe

      Like

  5. Hi all

    The folowing piece of code gets the first day of the next month. so if you have 20 january the function finds 1 february as result.

    DECLARE @datum DATETIME
    SET @datum=’2007-01-31′

    DECLARE @datFuture DATETIME
    SET @datFuture = dateadd(mm,1,@datum)

    DECLARE @dateFirstDayNewMonth datetime
    SET @dateFirstDayNewMonth = cast(year(@datFuture) as varchar(50)) + ‘-‘ + cast(month(@datFuture) as varchar(50)) + ‘-1′

    print @dateFirstDayNewMonth

    Like

  6. Hi. How do I get/print all the months between 2 dates, eg. 01 Aug 2006 and 31 March 2007? Eg.

    Aug 2006
    Sep 2006
    Oct 2006
    Nov 2006
    Dec 2006
    Jan 2007
    Feb 2007
    Mar 2007

    Like

    • declare @date1 datetime,@date2 datetime
      select @date1=’01 Aug 2006′, @date2 =’31 mar 2007′

      select dateadd(month,number,@date1) from master..spt_values
      where type=’p’ and number between 0 and datediff(month,@date1,@date2)

      Like

    • DECLARE @date1 DATETIME,
      @date2 DATETIME

      SELECT @date1 = ’01 Aug 2006′,
      @date2 = ’31 mar 2007′;

      WITH months(monthnames)
      AS (SELECT @date1 AS monthnames
      UNION ALL
      SELECT DATEADD(mm,1,monthnames)
      FROM months
      WHERE monthnames < @date2)
      SELECT STUFF(CONVERT(VARCHAR(12),monthnames,106),1,3,'') AS monthname
      FROM months
      OPTION (MAXRECURSION 0)

      Like

  7. Hi

    I have to create a query where the month we are selecting must match with the current month.
    Like if i select January and in database if there is 2 set of data , January and December, it has to select january data and display.

    Like

  8. hi…
    it s very useful to me…………thanx…….
    i have a problem that…..how can i display the group of date which should fall under 1st april n 31st of april ie one full month …like wise all the months…….??
    can u help me pls???

    Like

  9. use this simple query to get first date of last month

    select cast(cast(datepart(month, dateadd(mm, -1, getdate())) as varchar) +
    ‘/’ +
    cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))
    as varchar) + ‘/’ +
    cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

    Use this to get previous months last date

    SELECT convert(varchar,DATEADD (day, – datepart(dd,getdate()), getdate()),101)

    Like

  10. hi,good day…

    thanks for ure codings above….but iam having problem to select the month and display the details…eg…..i have a table with the field which would have the date,month and year (fldMonth), if let say i select the year 2007 in another drop down list, i want the table to display the details starting from june 2007 till july 2008…..

    i really have no idea…plz help me out…urgent…!!!currently my coding just display the details for that particular year onli from january to december…..

    thanks lots

    Like

    • Get idea from this and apply it

      declare @year int
      set @year=2007
      select dateadd(month,6-1,dateadd(year,@year-1900,0)),dateadd(month,6-1+13,dateadd(year,@year-1900,0))

      Like

  11. Worked like a charm and saved a few brain ticks from figuring it out myself.

    I used it for a financial report so I went a step further and did this for previous year dates. I also need to find previous year WTD so I used this:
    DATEADD(wk, DATEDIFF(wk, 6, dateadd(yy,-1,@dToday)), 6)

    Like

  12. How to get first and last working day of month? I mean exact DAY of week when we start working by excluding SATURDAY and SUNDAY if it comes on 1st or 2nd. So Query should return 3rd.

    Like

  13. /*To find first day of previous/next month*/
    declare @testdate datetime
    select @testdate=getdate()
    –Find first day of pevious month
    SELECT DATEADD(dd,1-day((DATEADD(mm,-1,@testdate))),DATEADD(mm,-1,@testdate))

    –Find first day of next month
    SELECT DATEADD(dd,1-day((DATEADD(mm,1,@testdate))),DATEADD(mm,1,@testdate))

    Like

  14. @nir

    use master
    create database “2008AUG”
    — give your options

    use double quotation around the name of the database and you can create a database name starting with a letter.

    Thanks,
    Imran.

    Like

  15. hi I want to get the last day of the month between the two given dates. also I want to get the last day of the week between the two dates.

    Like

  16. Thanks for the article, gave me clue for my requirements, I have jotted down calculating various days like this day last year, first day of prior years and so on, hope this would be useful.

    –This week start date Monday —
    select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
    –This week enddate–
    –select dateadd(d,-1,getdate())

    select cast(floor(cast(dateadd(d,-1,getdate()) as float)) as datetime)
    –Last week start date–
    select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)
    –Last week end date–
    select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
    –First day of this year–
    select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
    –Last day of the prior year–
    select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)

    –First day of next year–
    select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)
    –First day of prior year–
    SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)

    –This week last Year start date Monday —
    select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)
    –This day last year
    select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
    –First day of the current month–
    select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
    –Last day of the current month–
    select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)
    –First day of the current month Last year–
    select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
    –Last day of the current month Last Year–
    select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)

    Like

  17. Hi,

    Thnaks for your query, Is it possible that instead of sending date can I send only month. Could you please send me that query if possible.

    Thanks in advance.

    Regards
    Suman

    Like

  18. This article helped me in one query.But i’m trying another query,can you help me in ths?

    I have a Sales table data.I have user submitting every month sales data.My company has financial year from OCt 1 st to Sept 30 the next year.Whenever, i run the query, i have to get totalSalesAmount,for Salesdate which ranges between Oct 1st to till date.If i run that query now i have to get sales amount(Oct 2008+Nov 2008+Dec 2008) but when i run it in March 2009, it should should fetch (Oct 2008+Noc 2008+Dec2008+Jan 2009+Feb2009),
    How do i do that?
    Help appreciated

    Like

  19. Hi,

    For each month we have many weeks, some month will have 4 weeks but some will have 6 weeks ( for example for jan 09 , 01 -03 jan will be considered as Week1). How to find out the week number as per the above logic.

    If i give a date the query should return it belongs wich week of the month.

    i tried with datepart and week , but it returns week number for the year not for the month

    Thanks

    Like

  20. Hi,

    I want to claculate age say for eg.

    Tab1
    ——-
    contribution_date Dob AgeAtContribution
    15-12-2005 01-07-1977 ?

    Want to calculate age at the time of contribution_date with the help of dob column above.

    Thanks,
    aak

    Like

  21. Hi,

    Here you can use computed column to get Age At contribution Date.

    Example:

    CREATE TABLE t1(
    contribution_date DATETIME,
    Dob DATETIME
    )

    INSERT INTO t1 (contribution_date, DOB) VALUES(NULL, ‘1977-07-01′
    )

    ALTER TABLE t1
    ADD AgeAtContribution AS DATEDIFF(yy, DOB, contribution_date)

    SELECT * from t1

    You can also find my blog to add computed column at:

    http://tejasnshah.wordpress.com/2008/12/24/how-to-add-computed-column-sql-server-2005/

    Thanks,

    Tejas Shah

    Like

  22. How do you get last day of month prior year for February where prior year was leap year?

    Using the example above:

    declare @period smalldatetime
    set @period = ‘2/28/2009′

    select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,dateadd(mm,datediff(m,0,@period)+1,0)))as float)) as datetime)

    Result: 2/28/2008

    Need result to be 2/29/2008

    Like

  23. @Verdel

    Make the date March 1st, then subtract a day: select dateadd(yyyy, -1, dateadd(m, +1, dateadd(dd, – (datepart(dd, @period) – 1), @period)))

    Like

  24. Hello,

    I tried to create the SQL statement with the condition:

    “Today’s date > Month 36 after first VISIT_date” ?

    I would think about 36 months * 30 days as:

    (SysDate – VISIT_date) > (36*30)

    But not every month has 30 days. Can any one help pls?

    Thanks,

    -Vanessa

    Like

  25. @Vanessa

    How about: SysDate >= DATEADD(m, 36, VISIT_date)

    Though, 36 months is 3 years, so: SysDate >= DATEADD(yy, 3, VISIT_date)

    Like

  26. hi,
    am sreekanth, i have a table with some columns and date of update.i am writing function which takes month and year as parameters.i want max date of update from table where date of update <=(month and year) how to write a query.plz reply me to the mail if u know the solution

    Like

  27. @Tania.

    If you have SQL Server 2005 or higher , you can try this,

    create table #temp(id int identity, ename varchar(10))
    go

    insert into #temp values (‘Testing’)
    go 12 — This should not be replaced.

    go

    select datename (mm , dateadd (mm, id – (datepart(mm,getdate())), getdate())) Month_Names
    from #temp
    go

    drop table #temp
    go

    I am sure, there is a better way of doing this, at-least this should give you a start.

    ~ IM.

    Like

  28. I was going through this forum hoping to get the last day of year of a given date but can’t find how to get. Anyone can help me how to get it. I need this badly.

    Thanks.

    Like

  29. Haamidou,

    Try this,

    Select Datename ( dw, Dateadd ( dd , -1 , datename (yy, dateadd (year, +1, getdate()))))

    Output : Thursday

    Replace Getdate() with your date column name.

    Please test this before using it in your code.

    ~ IM.

    Like

  30. Hellooo…

    M the student of MCA n i hv such a query….

    der detail is as follow….

    -> PRINT THE INFORMATION OF CUSTOMERS WHO HAVE BEEN BORN IN THE MONTH OF FEBRUARY.

    Like

  31. I need to know the concept of Parameterized views?? Is it realted to functions returning table are parameterized views??

    How these are different then Stored procedures.??

    Please tell me!!

    Like

  32. Hi,

    I want query that will display the all the sundays with date that are coming in the whole year, or the year that i will pass as a parameter.

    Thanks in advance,

    regards,
    yogesh

    Like

  33. Hi,

    Yogesh you can find out all sundays for given year by this query.

    What I did is:

    First I get First Date of given year and Last date of given year. Then I used CTE (common Table expression), to get all dates.

    DECLARE @Year AS INT, @FirstDateOfYear DATETIME, @LastDateOfYear DATETIME
    SELECT @year = 2009
    SELECT @FirstDateOfYear = DATEADD(yyyy, @Year – 1900, 0)
    SELECT @LastDateOfYear = DATEADD(yyyy, @Year – 1900 + 1, 0)

    ;with cte as(
    SELECT 1 AS DayID,
    @FirstDateOfYear AS FromDate,
    DATENAME(dw, @FirstDateOfYear) AS Dayname
    UNION ALL
    SELECT cte.DayID + 1 AS DayID,
    DATEADD(d, 1 ,cte.FromDate),
    DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
    FROM cte
    WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
    )
    SELECT FromDate AS Date FROM CTE
    WHERE DayName LIKE 'SunDay'
    option (MaxRecursion 370)

    Let me know if you have any doubts.

    Thanks,

    Tejas
    SQLYoga.com

    Like

  34. Or you can use the below query :

    create table #temp (i int identity(1,1),c1 char(1))
    insert into #temp (c1) values(‘a’)
    go 1000
    select dateadd(“dd”,i,GETDATE()) dtDate from #temp where datename(dw,dateadd(“dd”,i,GETDATE())) = ‘Sunday’

    Regards,
    Pinal Dave

    Like

  35. Pingback: SQL SERVER – Get Date of All Weekdays or Weekends of the Year Journey to SQL Authority with Pinal Dave

  36. Dear Pinal Dave,

    i am new in database site but i got good knowledge from you blog nice efforts helping db developer by ur skill well dear i want help concerning date issue, will you solve “how to get all dates of current month.”

    thanks

    Like

  37. ;WITH CTE AS
    (
    SELECT DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()) AS FIRSTDATE
    UNION ALL
    SELECT DATEADD(dd,1,firstdate) from CTE where FIRSTDATE<=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,getdate())),101)
    )
    select CONVERT(varchar(25),FIRSTDATE,101) from CTE

    Like

  38. hey..
    I’m looking to get the day from a date.
    How do i get it?
    suppose I give some date format in the sql query, lets say for example “10-Apr-98″, I would like to get the result as which day this date has occurred? Like.. saturday or sunday .. etc??

    Can someone help me how to do it?
    Thx !!

    Like

  39. hello,

    I’m looking to get the for every day of the month only a special time..
    for example 01.04.2010-30.04.2010; every day 8-12 clock

    Can someone help me how to do it?
    thanks

    Like

  40. Hi,

    I want to get the fiscal period of any year for example

    Input : June 2010

    Results desired

    Period…………….Start…………………………………..End
    1…………………1-06-2010……………………………1-07-2010

    And so on untill May 2010 …what will be the query.

    Thanks in Advance

    Like

  41. Hi Josh,

    If you are using SQL 2005 and above edition, you can use Recursive CTE to generate this type of result.

    you can write following query:

    DECLARE @InputDate SMALLDATETIME, @OutputDate SMALLDATETIME
    SELECT @InputDate = ‘2010-06-01′
    SELECT @OutputDate = DATEADD(mm,12,’2010-06-01′)

    ;with cte as(
    SELECT @InputDate As StartDate,
    DATEADD(mm,1,@InputDate) AS EndDate
    UNION ALL
    SELECT DATEADD(mm,1,c.StartDate) As StartDate,
    DATEADD(mm,1,c.EndDate) AS EndDate
    FROM cte c
    WHERE DATEADD(mm,1,c.StartDate) < @OutputDate
    )
    SELECT *
    FROM Cte

    Let me know if it helps you.

    Thanks,
    Tejas
    SQLYoga.com

    Like

    • Thanks Tejas,

      I’ve solved it using below i think I didnt describe my output properly.

      DECLARE @StartDate DATETIME = ’06/1/2010′;

      WITH periods AS
      (SELECT 1 AS period
      UNION SELECT 2
      UNION SELECT 3
      UNION SELECT 4
      UNION SELECT 5
      UNION SELECT 6
      UNION SELECT 7
      UNION SELECT 8
      UNION SELECT 9
      UNION SELECT 10
      UNION SELECT 11
      UNION SELECT 12
      )

      SELECT period,
      DATEADD(month,period-1,@StartDate) as startDate,
      DATEADD(day,-1,DATEADD(month,period,@StartDate)) as endDate
      FROM periods

      Like

  42. Hi,
    I need to calculate age of a member turning 18 this year starting from current month to this year.
    I’m working on this formula
    (DATEDIFF(year, bdate, DATEADD(s, – 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) = 18)
    this gives me members turning 18 starting from January. Can anyone help me out in this.
    Thanks

    Zara

    Like

    • Also, if i want to get age for certain months, for example members turning 18 from August to November or from January to March.

      I appreciate your help

      Like

  43. I need to two reports getting the 1st thru the 15th and the 16th to the end of the month. The first report runs on the 16th of the month and the 2nd report runs on the 1st or the next month.

    Like

  44. HI
    I want to get start date and end date of financial year.
    My input will be year and according to it I should get start date and end date of fiscal year

    Thanks

    Like

  45. DECLARE @TodayDate DATETIME,
    @PreviousMonth DATETIME,
    @PreviousMonthLast DATETIME

    select @TodayDate = ’02-Mar-2010′
    print @TodayDate

    –Previous Month
    SET @PreviousMonth =DateAdd (MONTH, -1, @TodayDate)
    print @PreviousMonth

    –1st day of PreviousMonth
    SET @PreviousMonth = DateAdd(D, ( -DAY(@PreviousMonth) ) + 1, @PreviousMonth)
    print @PreviousMonth

    –last day or previous month
    SET @PreviousMonthLast = DateAdd(DAY, -1, DateAdd(MONTH, 1, @PreviousMonth))
    print @PreviousMonthLast

    Like

  46. HI,

    I am working on a report and I have a where condition in the query where( Time between (@Startdayofweek) and (@Enddayofweek)). If I select a Month then it show all the values for @EndDayofWeek(i.e.,fridays) and @startdayofweek (i.e., Mondays) should select values based on the @Enddayofweek. I am able to get all the values for @Enddayofweek(i.,fridays) but @startdayofweek parameter is taking only one value from the @Enddayofweek. here is my query for @enddayofweek and @Startdayofweek

    @Enddayofweek=select CONVERT(VARCHAR(12), DATEADD(DD, 6 – DATEPART(dw,day), day), 10) as enday
    where month=’feb’ and year=’2010′
    (I have a table called time and in that table it has days,year and month)

    @Startdayofweek= SELECT CONVERT(Varchar(10), DATEADD(day, – 6, @enddayofweek), 101) AS startday

    Please help me with this

    Thanks,
    Kumar

    Like

  47. Hi there,
    Very helpful content. Would you please help me to know how to get the last day of next month ? As a suggestion, it would be nice if you give a short description of what your statement is doing or how it is calculating.

    Thanks

    Like

    • select dateadd(month,datediff(month,0,getdate())+2,-1)

      The datediff function calculates the difference between based date 1900-01-01 to current date. 2 is added to get the date of two months greater than today. The month difference is added to based date again and to create a first day and subtracting 1 will give you last data of next month

      Like

  48. HI……….
    I AM WORKING ON A REPORT
    I HAVE TABLE LIKE

    IMG_NO FLAG LOG USR_ID

    W01 1 4/5/2011 1
    W01 4 4/5/2011 1
    W02 1 4/5/2011 1
    W03 1 4/5/2011 1
    W03 2 4/5/2011 1
    W04 5 4/5/2011 2
    W05 1 4/5/2011 2
    W05 4 4/5/2011 2
    W05 5 4/5/2011 2
    W06 1 4/5/2011 2
    W06 1 4/5/2011 2

    SO I WONT OUTPUT LIKE

    USR_ID STARTIMGNO ENDIMGNO FLAGIS4 FLAGIS’5 TOT
    1 W01 W03 1 0 5
    2 W04 W06 1 2 6

    THE ABOVE OUTPUT BASED ON DATE PLZZZZZZZZZZZZZZZ
    HELP ME

    THANKS.

    Like

  49. HI……….
    I AM WORKING ON A REPORT
    I HAVE TABLE LIKE

    IMG_NO FLAG LOG USR_ID

    W01 1 4/5/2011 1
    W01 4 4/5/2011 1
    W02 1 4/5/2011 1
    W03 1 4/5/2011 1
    W03 2 4/5/2011 1
    W04 5 4/5/2011 2
    W05 1 4/5/2011 2
    W05 4 4/5/2011 2
    W05 5 4/5/2011 2
    W06 1 4/5/2011 2
    W06 1 4/5/2011 2

    SO I WONT OUTPUT LIKE

    USR_ID STARTIMGNO ENDIMGNO FLAGIS4 FLAGIS’5 TOT
    1 W01 W03 1 0 5
    2 W04 W06 1 2 6

    THE ABOVE OUTPUT BASED ON DATE PLZZZZZZZZZZZZZZZ
    HELP ME

    THANKS.

    Like

  50. Wants to calculate weekly data from Monday to Sunday basis. But the doubt is, when i am calculating the data by year. weeks got divided into two parts, i.e current year and next year. i want to combine into one.

    pls help some one.

    Like

  51. Hy, cool stuff! Perhaps you can help me with my problem. i need a query for getting the first day of any month as a a start day.

    can you perhaps help me?

    cheers stephan

    Like

  52. Can anyone please help me to print last seven days dates in sql. like below
    today- 17/8/2011
    16/8/2011
    15/8/2011
    14/8/2011
    ……

    Like

  53. select ‘FirstDay Of The Month’ as ‘Text’,convert(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106) ‘Date’
    union all
    select ‘LastDay Of The Month’,convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),106)

    Like

  54. Hi,

    I need a Financial year end date for my report ie., Today date is ‘2012-01-07′ then Financial year end date should be ‘2012-03-31′ similarly if today date is ‘2012-04-01′ then Financial year end date should be ‘2013-03-31′. Please help me out….

    Like

  55. declare @i int
    set @i = 1
    declare @date smalldatetime
    drop table #days
    create table #days(day_i smalldatetime)
    set @date= ’02/01/2011′
    insert into #days values(@date)
    while (@i<31)
    BEGIN
    –select @date+1
    insert into #days values(@date+1)
    set @i = @i+1
    set @date = @date +1

    END

    delete from #days where datepart(M,day_i) datepart(M,’02/01/2012′)
    select * from #days

    select min(day_i) as first_day,max(day_i) as last_day
    from #days

    Like

  56. Hi… I need a query to calculate running total of a particular column.But, it only display the final total in the last cell…

    Like

  57. Hi… I need a query to calculate running total of a particular column.But, it only display the final total in the last cell…

    It looks like:
    amount Total
    ———- ——-
    10
    20
    30
    10 70

    Please help me……

    Like

  58. Hi….I need a query to get all the previous for the given month..for eg:If I enter 3 the query should display all Months before 3 i.e jan,feb,march……Please help me………

    Like

  59. This way is very short and best to get DayOfMonth. if u like then plz tell me.

    DECLARE @SystemDate DateTime, @StartDate DateTime, @EndDate DateTime
    SET @SystemDate = ’26-Apr-2012′
    SELECT @StartDate = DATEADD(dd, -Day(@SystemDate) + 1, @SystemDate)
    SELECT @EndDate = CONVERT(VARCHAR(20), DATEADD(dd, -(DAY(DATEADD(mm, 1, @SystemDate))),DATEADD(mm, 1, @SystemDate)),101)
    –SELECT @StartDate StartDate, @EndDate EndDate
    SELECT DateDiff(WeekDay,@StartDate,@EndDate) + 1 AS DayOfMonth

    from shah…

    Like

  60. In Application take datetimepicker
    & set format on load event
    string fDate = string.Format(“{0:MMM-yyyy}”, dtpFromDt.Value);
    it will show Dec-2010

    Rajshri

    Like

  61. I used the code above to create a function i use to find the 1st of the month. Use – argument for past and positive for future or 0 for first of present month.

    CREATE FUNCTION dbo.Get1stMonthDateFromNow
    (
    @Months INT
    )
    RETURNS DATETIME
    AS
    BEGIN
    DECLARE @CurrentMonth DATETIME
    SET @CurrentMonth = CAST(
    CONVERT(
    VARCHAR(25),
    DATEADD(dd, -(DAY(GETDATE()) -1), GETDATE()),
    101
    ) AS DATETIME
    )

    RETURN DATEADD(m, @Months, @CurrentMonth)
    END
    go
    SELECT dbo.Get1stMonthDateFromNow(0)

    Like

  62. Lazy solution: for fast day of the month

    select ‘1/’+
    CONVERT(varchar(25),(select MONTH(getdate())))
    +’/’+
    CONVERT(varchar(25),(select YEAR(getdate()) ))

    Like

  63. BUDDHI Srilanka

    Some one need to get previous year Current month start and end date

    DECLARE @mydate DATETIME
    DECLARE @FdayOfCurrentMonth DATETIME
    DECLARE @LdayOfCurrentMonth DATETIME
    DECLARE @FdayOfCurrentMonthLY DATETIME
    DECLARE @LdayOfCurrentMonthLY DATETIME
    BEGIN
    SELECT @mydate = GETDATE()
    SELECT @FdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
    SELECT @LdayOfCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
    SELECT @FdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-12,@mydate)),101)
    SELECT @LdayOfCurrentMonthLY = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,-11,@mydate)),101)

    Like

  64. Hi I am in the way of automating my SQL query. I have a filed called Transaction_Date in my table.

    I my analysis i will pre period analysis, promo period analysis and post period analysis. FOr ex, if the campaign starts on 1-June-2012 and the end date is on 30-Jun-12, then i should take the same number days as promo period for pre and post anlaysis.

    I am giving the dates manually in hte SQL code. But i want to automate it by considering the dates automatically.

    drop table #response_curve
    select Status,
    week_no,
    [Customers] = count (distinct account_no),
    [Transactions] = sum(case when tx_type_code in (’01’,’90’) then 1 when tx_type_code in (’03’,’94’) then -1 else 0 end),
    [Spend] = sum(cast(total_txn_value*issue_mult as bigint)),
    [Standard_Points_Issued]= sum(cast(std_points_value*issue_mult as bigint)),
    [Bonus_Points_Issued] = sum(cast(bonus_points_value*issue_mult as bigint))
    into #response_curve
    from
    (select case when t.transaction_date >= ‘2012-07-17′ and t.transaction_date = ‘2012-07-22′ and t.transaction_date = ‘2012-07-29′ and t.transaction_date = ‘2012-08-05′ and t.transaction_date = ‘2012-08-12′ and t.transaction_date = ‘2012-08-19′ and t.transaction_date = ‘2012-08-26′ then ‘Week7’end as week_no,
    t.account_no,tt.tx_type_code ,t.total_txn_value,tt.issue_mult,t.std_points_value,t.bonus_points_value,t.transaction_date,f.Status
    from tx_txn t with
    inner join #final_campaign_members f on f.account_no = t.account_no
    inner join tx_type tt on t.tx_type_code = tt.tx_type_code
    where t.terminal_id like ‘RSA%’ and t.transaction_date >= ‘2012-07-17′ and t.transaction_date < '2012-09-01' ) a
    group by Status,week_no

    Above is the code which i am using. please help me on howto automate the above code by not giving the dates manually

    Like

  65. Hi I need a help with the following criteria in SQL query.

    I have an end date of say ex. 4/15/2012 and I want to set this date to end of the month ie, 4/30/2012.

    Similarly, I have a beginning date as 4/16/2012 and I want to set it first of next month ie 5/1/2012. Can someone help me on this plz.

    Like

  66. Hi all,
    I need a querry that can give me endate from start date and number of months

    Like I have a start date is 10/1/2012 and number of months is 10, how can i get 7/31/2013

    Like

  67. Here’s the correct version for the blogger’s queries that takes into account the times 00:00:00 and 23:59:00

    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0), ‘Start Last Month’
    SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)), ‘End Start Last Month’

    SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), ‘Start This Month’
    SELECT DATEADD(minute, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)), ‘End This Month’

    Like

  68. i need to fetch all the details fom database by filtering name and from(startdate) to (enddate) if a name is kannan i need all his details such as projectname,task details within that filtered date for timesheet eg he done various task and list all the task he made

    Like

  69. Do not use string concatenation. Use DATADD/DATEDIFF functions. Run the below query

    SELECT
    DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) ThisQuarterFirstDay,
    DATEADD(QQ, 0, GETDATE()) ThisQuarterLastDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) – 1, 0) LastMonthFirstDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), -1) LastMonthLastDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) ThisMonthFirstDay,
    DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, -1) ThisMonthLastDay

    Like

  70. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  71. Can also do it like this:
    Will get the first of the month 2 months ago, and first of the months 4 months in the future
    SELECT DATEADD(month, -2, GETDATE() – (DAY(GETDATE()) -1)), DATEADD(month, 4, GETDATE() – (DAY(GETDATE()) -1))

    Like

  72. how to get first Monday in next year, by using Ms sql server 2008.Below following code will return the result as a specific(using static value +7) but i want generalized(dynamically change)query

    select
    (dateadd(day,(365-DATEPART(DY,GETDATE())+7),GETDATE())) as First_Monday_Next_Year

    Please help me………..[email removed]

    Like

  73. Pinal, everytime I have a SQL question, I google around and your awesome answers keep coming up. Thank you many many times!

    Like

  74. If SQL Server was a country, you’d be on that country’s Mt. Rushmore. If SQL Server was a NFL team, you’d be the franchise player. Who’s the man? Pinal Dave is the man.

    Like

  75. I need to run a report on monthly basis.
    My requirement is when ever i run the report ,the query should take first and last date of previous month using sysdate.Can Some one help me on this

    Like

  76. Hi Pinal ,

    I think its good solutions but can I suggest not converting to varchar as depending on what people do with the code after it could be wrong. ie there using an Australian Date format vs US date format etc

    I modded the solution for the first day of the month

    –first day of month
    –with time zeroed out
    SELECT CAST(DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as date)) as datetime)
    –with time as it was
    SELECT DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as datetime))

    Would just need to replace getdate() with the whatever date they want to pass in.

    P.S. love your blog. Its been really helpful

    Like

  77. Thanks. Past few months I was using this code, but today..

    I tried to get the first day of the 4th month, where current day is in October. Instead of giving me ‘2/1/2014′, it gave me ‘1/30/2014′.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s