Post reply

List month names

  • February 13, 2008 at 4:55 am

    #95705

    Hi,

    I am just wondering if anyone knows how to list the month names between two specified dates, I tried to find it a few places but no luck

    Thanks in advance

    Tim

  • February 13, 2008 at 5:13 am

    #777756

    SELECT DISTINCT DATENAME(MONTH, SeqDate)

    FROM dbo.fnSeqDates('20070901', '20080201')

    http://qa.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/[/url]

    N 56°04'39.16"
    E 12°55'05.25"

  • February 13, 2008 at 5:25 am

    #777759

    Hi,

    Thanks for your reply, i think i am 90% there but when i try to introduce my dates to the query i get the following error

    'StartDate' is not a recognized OPTIMIZER LOCK HINTS option.

    This is the new query where i join the Seasons table and try to get the months between these dates but if i pass in the dates as '07/01/07' and '06/30/08' it works fine any ideas?

    SELECT DISTINCT DATENAME(MONTH, SeqDate)

    FROM dbo.fnSeqDates(StartDate, '06/30/08')

    Join Seasons sea on sea.ID = 148

    The season table is just ID, Name, StartDate and EndDate thanks again for your help

    Regards

    Tim

  • February 13, 2008 at 5:29 am

    #777760

    You can't do this in SQL Server 2000. It is with SQL Server 2005 you can use CROSS APPLY.

    N 56°04'39.16"
    E 12°55'05.25"

  • February 13, 2008 at 5:31 am

    #777762

    select top(12) identity(int, 0,1) AS nr into #tally from sys.all_objects

    DECLARE @startDate DATETIME

    DECLARE @endDate DATETIME

    SELECT @startDate = '03/03/2007'

    , @endDate = '08/08/2007'

    -- set it to first day of the month

    SELECT @startDate = DATEADD(DD, 1 - DAY(@startDate), @startDate)

    SELECT DATENAME(month, DATEADD(month, nr, @startDate))

    FROM #tally

    WHERE DATEADD(month, nr, @startDate) >= @startDate

    AND DATEADD(month, nr, @startDate) <= @endDate

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • February 13, 2008 at 9:22 am

    #777913

    Hi,

    Thanks for your replies guys i managed to put some pieces of code together and get what i needed

    SELECT Distinct Substring(DATENAME(MONTH, SeqDate), 0, 4) as 'Month',

    Month(SeqDate) as 'MonthID',

    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(SeqDate)-1),SeqDate), 103) as 'StartDate',

    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,SeqDate))),DATEADD(mm,1,SeqDate)),103) as 'EndDate'

    FROM dbo.fnSeqDates('07/01/07', '06/30/08')

    Join Seasons sea on sea.ID = 148

    Thanks again

    Tim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply