Concatenation of Date field or Date only - no timestamp

If you want to select rows for only a particular date(s), this is the best way to do it:


--assumes CalendarDate column is a DATETIME/SMALLDATETIME type

SELECT     FCStoreCount.StoreCountID, FCStoreCount.DemandSrcID, CURRENT_TIMESTAMP AS PrvMth
FROM         FCStoreCount INNER JOIN
                      FiscalCalendarDaily ON FCStoreCount.FYPeriod = FiscalCalendarDaily.FiscalMonth
WHERE  FiscalCalendarDaily.CalendarDate BETWEEN CONVERT(VARCHAR(8), GETDATE(), 112) AND CONVERT(VARCHAR(8), GETDATE(), 112) + ' 23:59:59.997'  --if DATETIME
-- if SMALLDATETIME, use ' 23:59' instead of ' 23:59:59.997'

What I am trying to accomplish is to determine the previous months Fiscal Period and retrieve the data based on that date.

I need to retrieve the storecount for 11/1/2004, my calander table has the actual date linked to the fiscal period which is inturned linked to my fcstorecount via the FYPeriod('12/1/2004')

any suggestions.
Karen

Getting the next months values if the year changes current mth = 12/1/2004 - next month = 1/1/2005

What is the proper syntax?

SELECT     FiscalCalendarDaily.CalendarDate, FiscalCalendarDaily.FiscalMonth
FROM         FiscalCalendarDaily INNER JOIN
                      FCStoreCount ON FiscalCalendarDaily.FiscalMonth = FCStoreCount.FYPeriod
WHERE     (FiscalCalendarDaily.CalendarDate = iif(Month(CONVERT(datetime, CONVERT(char(12), GETDATE()), 12, DateDiff(y, 1, DATEDIFF(m, 1,
                      CONVERT(datetime, CONVERT(char(12), GETDATE())))), DATEDIFF(m, 1, CONVERT(datetime, CONVERT(char(12), GETDATE()))))

karen

SELECT     FiscalCalendarDaily.CalendarDate, FiscalCalendarDaily.FiscalMonth
FROM         FiscalCalendarDaily INNER JOIN
                      FCStoreCount ON FiscalCalendarDaily.FiscalMonth = FCStoreCount.FYPeriod
WHERE FiscalCalendarDaily.CalendarDate = CONVERT(char(10), DATEADD(m, 1, GETDATE()), 101)

or

SELECT     FiscalCalendarDaily.CalendarDate, FiscalCalendarDaily.FiscalMonth
FROM         FiscalCalendarDaily INNER JOIN
                      FCStoreCount ON FiscalCalendarDaily.FiscalMonth = FCStoreCount.FYPeriod
WHERE FiscalCalendarDaily.CalendarDate = CONVERT(char(10), DATEADD(m, -1, GETDATE()), 101)