.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

If a date is null or doesn't exist return another date

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server


I have 2 dates which are calculated members:

SELECT ReportingMonday ON 0
FROM DataCube

SELECT [FirstCubeDate] ON 0
FROM DataCube

ReportingMonday= StrToSet("[Dim Date].[Date Key].&[" + IIF(WEEKDAY(NOW())<=2,FORMAT(DATEADD("W",-5-Weekday(NOW()),CDATE(Now())),"yyyyMMdd"),FORMAT(DATEADD("W",2-Weekday(NOW()),CDATE(Now())),"yyyyMMdd")) + "]")

FirstCubeDate = HEAD([Dim Date].[Date Key].[Date Key].Members,1)

What I want is a named set to say:

if ReportingMondayis null or blank (so there isn't a report monday e.g. the data started on Tuesday) then return FirstCubeDate

Any Ideas?




View Complete Post

More Related Resource Links

returning a date with a count of zero if it doesn't exist in the date range?


i have the following query.

SELECT DATEADD(dd,0, DATEDIFF(dd,0,accesseddate)) AS hitdate, COUNT(hitid) AS hitsperday
FROM hits h
ON h.urlid = u.urlid
WHERE (accesseddate BETWEEN DATEADD(d, -6, GETDATE()) AND GETDATE() AND u.userid = @userid)
GROUP BY DATEADD(dd,0, DATEDIFF(dd,0,accesseddate))

but if there were no "hits" for a particular date range in the last week i only get the dates returned where there were hits. i need to get all the days returned and where there were no results, i need a zero returned.

hitdate            hitsperday

2010-10-07     2
2010-10-06     58
2010-10-04     1645
2010-10-03     1192 

what i need to return is this:     

hitdate                 hitsperday

2010-10-08     0
2010-10-07     2
2010-10-06     58
2010-10-05     0
2010-10-04     1645
2010-10-03     1192

any suggestions?

thank you very much.

Updating a List item field value in a SPD Workflow is setting Date fields to null

Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Not Null Date Default

I'm one of the people that try to avoid nulls when ever possible, usually using defaults.  This is especially important since my apps do a lot of data exchange with external entities often involving flat files.  A flat file can't distinguish between a null or an empty value so to ensure consistancy between data types in both systems I try to make sure every field has some value.  (E.g., If I have a null or empty string in a char field type a CSV flatfile shows ',,' for both.  The target system might interpret this as an empty string or a null; I have no direct control.  This could result in a null in one system and '' in another losing consistancy.  For charactor and number types this is rarely a problem if I communicate that there are no null fields in the data, but for dates it often becomes an issue. Is there a industry standard approach to managing "empty dates" so I can make date fields not null and empty? Thanks

How to create a calcuated member, which will always return the date one month before the currentmemb

I need to create a calculated member in the cube script, which will be subsequently used in various other script calculations.  It needs to return the date, which is 1 month before the currentmember of the Date dimension. I tried something like this: CREATE MEMBER CURRENTCUBE.[Measures].[LastPD]   AS           PARALLELPERIOD([Date].[Calendar].[Month], 1, [Date].[Calendar].currentMember) ,VISIBLE = 1; However when I then try to check the values with this query: SELECT [Measures].[LastPD] on 0, [Date].[Calendar].[Date].members on 1 FROM [TravCSAT] ; I only get NULLs for [LastPD].  Any idea what I am doing wrong?

how to force null in date field - access 2007 ?

hi i have table that contain date field. how i can force null or empty value on this field ? thank's in advance

ASP.Net / ODP.Net setting null date

Using VS2008, c# and ODP.Net(   In the following procedure  a_end_date  may or may not be null. If the input string is null, I tried to set  a_end_date to null. It is giving error.  The same result if I try to set it to dbNull.   How do I set null to a date field   Thanks Suresh   public string InsertOnlineApplication( string a_start_date,                                           string a_end_date,                                           string a_status,                                           string a_app_type) {              OracleGlobalization info = OracleGlobalization.GetClientInfo();            &nbs

Date maskedEdit with watermark doesn't update on invalid date


I'm trying to use the TextBoxWatermarkExtender with MaskedEditExtender/Validator and have found a bug (I think).

In the textbox I enter an invalid date such as 55-55-5555 and exits the textbox. I would then expect the watermark to be shown, but it doesn't. Instead the prompchar is shown (like __-__-____).

If I simply give focus to the textbox and then another control, the watermark is shown.

Is there a workaround such as manually force the TextBoxWatermarkExtender to update.

I'm not the best with javascript, I tried to call focus() and blur() on the textbox in the textbox's onBlur event using window.setTimeout. This works in Firefox, but not IE8, also there has to be a better way I think :-)

            <asp:TextBox ID="TextBox5" runat="server" MaxLength="1" onBlur=""/>
            <asp:ImageButton ID="ImgBntCalc" runat="server" ImageUrl="~/images/Calendar_scheduleHS.png" CausesValidation="False" />

        <ajaxToolkit:TextBoxWatermarkExtender ID="TBWE2" run

remote execution of ssis package using wcf doesn't like date parameter


I have a ssis package remotely executed by a C# windows application that runs ok in context of SSIS installed on the desktop; I created a wcf windows service that the C# application executes, creates a copy of the package with the date in the variable, but errors on the first SQL task component that requires the date. The copy executes using the SSIS utility. What is possibly the issue with wcf and the date type in the SSIS package? Here is the error:

OnError,SRVR2BLADEA3,MIC\1347259,Clear CII ImportDate,{AB3CF5BA-C114-40F6-9ECB-4C7620D97829},{8B1C10CB-580F-4BFE-BC7D-0B97C181E307},9/27/2010 3:29:56 PM,9/27/2010 3:29:56 PM,-1073548784,0x,Executing the query "" failed with the following error: "The type initializer for '<Module>' threw an exception.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the code:

case @"E:\SSIS Packages\CIISCImport.dtsx":

//string dtValue = (prms[iPrms - 1].Split('=').GetValue(1).ToString()) + " 12:00";

vars["User::ImportDate"].Value = DateTim

return records based on current date?


I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010

Return Last 3 Months data based on the DATE


Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?

adhoc reporting - data doesn't refresh on date range change


I created an adhoc report model using SQL Server 2008 reporting services. I am using data from a SQL 2000 database sitting on a SQL 2000 server. I created reports with start date and end date parameters, everything worked fine.


Now my ad hoc report model uses a SQL 2000 database (this database is a replication of the database I was using)  sitting on a SQL 2008 server. When I change parameters like start date and end dates, data doesn’t refresh. It shows the same data for any date range.


Has anyone seen this problem? What could be done to fix the issue. Is there any setting on the SQL server to fix this?



Problem inserting a null value into date column in SQL table


Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

Text from RequiredFieldValidator doesn't clear after picking date from jQuery UI DatePicker


I'm trying to use a RequiredFieldValidator with a TextBox that has a jQuery UI DatePicker attached to it. I'm finding that selecting a date from the datepicker does not trigger the RequiredFieldValidator to recognize that a value is now present. Thus, only server side validation is working for the RequiredFieldValidator and not client side. Any ideas on how I may fix this issue so both client side and server side validation fires?

Below is some sample code of this issue. To reproduce this situation: 1) Click the PostBack button (RequiredFieldValidator will indicate field is required) 2) Pick a date using the date picker 3) Notice that the RequiredFieldValidator's text still displays when it shouldn't.

<%@ Page
    Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">




        rel="stylesheet" />


How do I get the last date if selected does not exist?


Hi All,

My table

Date, Currency
10.10.2010 EUR
19.10.2010 USD
20.19.2010, EUR
21.10.2010, USD

If I have a date 9.10.2010, choose the nearst date 10.10.2010 EUR

If I have a date 18.10.2010 (this date does not exist), choose the nearest date 19.10.2010

If I have a date 25.10.2010, choose 21.10.2010 USD

How can you help me?


Active Service Requests based on start and end date, where date is null or in range



I have FACT and DATE dimension

ServiceRequestFactID          StartDateId           EndDateId

100              20090401              20090501
101              20090501              NULL
102              20100301              20090401

       DateID              FYear     FQuarter     FPeriod
       20080401          2009        Q1            01
       20080429          2009        Q1            02


Some service requests do not have end date, which means that they have service request all the time.

Date Hierarchy:   FYear -> FQuarter -> FPeriod. (Note: the hierarchy is not till the date level)


MDX Help: How to calculate the acti

Testing for Valid Date or Null Value -- Using Conditional Split


I have a Flat File Source with several columns of data that should be valid dates.  Sometimes however they may either be NULL or not valid dates.  I understand the expresstion ISNULL([ColumnName]) will test for the NULL condition.  What is the syntax to test if the value is a valid date?  It might be a string such as "baddate" or it could be a date that is too old.  Could you provide an example of an expression that will test for both of these conditions?




Move up a time hierarchy from current date to return a set of quarters


I have this calculated set to return the previous 3 months.  How do I rewrite to get the last 3 quarters? 



(3,strtomember("[Date].[Month].&[" + Format(Now(),"yyyy-MM") + "-01T00:00:00]").prevmember)

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend