Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

Hi there,

It is required to retrieve the records where ONLY Start Month, Year AND End Month, Year are passed. Trying below SQL to get these with no success. 

registrationdate BETWEEN(MONTH((registrationdate) = 01) AND (Year(registrationdate)= 2009)) AND ((Month(registrationdate) =03) AND (Year(registrationdate)=2010))

Help is appreciated.

Hi, most time dimensions are setup using a base Date field in the fact table, and they have plenty of issues for time analysis as it is. However my fact sales records have the time aspect assigned by pre-calculated periods, because depending on various factors, monthly final invoices are all raised on varying days (usually 2nd friday of month but can change). The monthly period is therefore not a straight calendar month. Probably a very common scenario.

So, the invoicing system already assigns the year (ie 2009, 2010, 2011) and monthly period (1, 2, 3 ... 12 with 3 representing march, even though that might represent 13th march to 9th april) and I want to use those as Time dim so we can do YTD, growth-on-prev-year etc.

It looks like its best to setup 2 Dimensions to link to 2 DataColumns/Attributes in the Fact table (say, FYear and FMonth, both integers). That way I can assigned attribute names like March to key column 3. If I combined them into 1 dimension with both fields making up a single key column, would have to either repeat the month names or link it to another Star schema I believe.

I can use the Add Business Intelligence wizard to make the Dimensions into Time ones instead of regular but I'm still not totally sure if this is the best structure/method and once done, how to use the YTD calcs to show in the cube browser (and my MDX knowle

I've created a matrix report that displays the quantity of different products  set to expire by month/year. The stored procedure returns records for a variety of products and each record contains an expiration date. If there are no products/records that contain an expiration date of lets say 6/2010 then 6/2010 will not appear as a column in the report.

I need a way to force these month/year columns to appear in the report.

Does anyone have a suggestion on how I can make that happen?

Rick Dowdall

I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

I have this date format from

(CHAR(10),(INVC_DT),112) = 20090102 

but I just need the month and year in order to do the aggregations with...

I need to convert an invc_date from 2009-01-01 00:00:00.000 to MM-YYYY

I have tried some of the stuff but not quite working for me yet

How do I write my SQL so I get this result?

2010
Januari(4)
Februari(23)
Mars(12)
April(5)
... and so on
2009
Januari(12)
Februari(2)
... and so on

I'm building a blog and want to have this as links. I have a field in the database "CreateDate".

Hello,

I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Hello,

I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Hi

How do I get my Datetime out in 3 dropdownlists :

ddlDay = Day

ddlMonth = Month

ddlYear = Year

Thanks

When below SQL is used to print the MONTH Names in SQL SERVER 2000, order by Month name ordering in Alphabetical order, which is incorrect.

Let me know any work around to get this order by Montly order and then Year.

SELECT CASE 
         WHEN MONTH(a.registrationdate) = 1 THEN 'Jan' 
         WHEN MONTH(a.registrationdate) = 2 THEN 'Feb' 
         WHEN MONTH(a.registrationdate) = 3 THEN 'Mar' 
         WHEN MONTH(a.registrationdate) = 4 THEN 'Apr' 
         WHEN MONTH(a.registrationdate) = 5 THEN 'May' 
         
 SPQuery to get all Items Modified in Current Month/Year 
  
I have a SharePoint List. Using CAML I need get all the Items that are modified in the current Month/Week/Year.? 
Can anybody help me to achieve this?
 
Thanks
R.Manimaran