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

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

Not Null Date Default

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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

SSRS 2008 R2 - OnPostBack from ReportParameter - if no default set, throws Value can not be null

SSRS 2008 R2 - OnPostBack from Parameter selection - if no default set, throws Value can not be null java script error. This does not happen with SSRS 2008, only with R2. and only with report parameters that do not have a 'default' set.  Any suggestions would be greatly appreciated.

Change the Default Date in the Date Picker Control

Hi, I have a WPF Datagrid in which one of the columns is a Date Column. So i have used a DataTemplateColumn as Follows     <my:DataGridTemplateColumn CellTemplate="{StaticResource EffDateDateTimePickerControl}"   CellEditingTemplate="{StaticResource addrEffDate}" Header="Effective Date"/> And in my Resource File i have written the following code: Style   TargetType="{x:Type my:Calendar}" x:Key="CalenderControlTemplate">     <Setter Property="Template">     <Setter.Value>     <ControlTemplate TargetType="my:Calendar" >     <my:CalendarItem Name="myCalendarItem"   Background="White"   BorderBrush="Black"   BorderThickness="1"   VerticalAlignment="Center" />     </ControlTemplate>       </Setter.Value>     </Setter>     </Style>       <DataTemplate x:Key="EffDateDateTimePickerControl">     <Label x:Name="lblEffDate" Content="{Binding effectiveDate,Mode=TwoWay}" ></Label>     </DataTemplate>     <DataTemplate x:Ke

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

How to get Default value in table to work when NULL in DataSet

Is this possible? If the parameter value is NULL in DataSet, the default value is not assigned.

Default date selection in SSRS with SSAS cube as a source




I am fairly new to SSAS and I am hoping that someone can help me with an issue I am experiencing.  I am create a SSRS report that goes against a SSAS cube.  My cube has a Date dimension with a Fiscal Year - Fiscal Quarter - Fiscal Month hierarchy.  In my report I have created filters for a From Date and a To Date, which are populated with the hierarchy of the Date dimension.


I need to default the filters as follows:


To Date = current quarter (that is the last quarter with fact data)

From Date = the To Date quarter from the previous year (e.g. if To Date quarter = Q1 2008, then From Date quarter needs to be Q1 2007)


I found some SSAS code that allows me to get the index of the last quarter with data, but I am stuck at this point.  I am not sure how I can translate this into a default selection for the To Date SSRS report parameter.  I also realize that there may be a better approach.



how set the default value for date inSQL Server?



i need to set a default value for a column of date

i need when the column dont get a date automatic set 1/1/1900 for it.

can you please help me?

Changing Default Language for British Date Format




After reading several topics about Datetime formats from US-English to British, I got more confused.  I have successfully changed Default Language on my local machine's SQL Server Express using this:

EXEC sp_configure 'default language', '23' 

But I can't change it on a Server side.  It prompts me to run 'RECONFIGURE' again and when I do I am getting this error:

"Address Windowing Extensions (AWE) is not supported in this edition of SQL Server."


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)

help inserting an empty string and a default date thru a stored procedure


I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (











this dont work



Default (but overrideable) date in DetailsView problem


I'm using a DetailsView control in Insert Mode (GridView for editing). I want to have today's date default into my date field, but the date has to be able to be overridden by the user.  

I can bind it to the database element (1) OR I can default today's date (1) , but seeming not both. What am I doing wrong?

(1) - Bind to database element

<asp:TemplateField HeaderText="Date" SortExpression="EmployeeTimeDate">


      <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("EmployeeTimeDate") %>'></asp:TextBox>



      <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("EmployeeTimeDate") %>'></asp:TextBox>



      <asp:Label ID="Label5" runat="server" Text='<%# Bind("EmployeeTimeDate", "{0:d}") %>'></asp:Label>



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?




What is the default in the Three State Workflow for how many days after due date to send email remin

I am setting up Three-State Workflow on an Issue Tracking list in MOSS 2007. My company does not allow SP Designer. I would like to know what is the default setting in this workflow for how many days after task due date to send the email reminder. Also, is there a way to change this setting under site collection administration? I would like it to send the reminder every 2 days.

SSAS 2008 - Date Member Property gets converted from NULL to (Blank)


Hello everyone,

I have this cube with a dimension of investment deals.  The "Deal" member has a attribute named "Latest Acquisition Date" where this date can be null for some deals.  Once the cube is processed, the null dates always get converted to "(blank)" either in BIDS cube browser or in Excel.  This happens no matter what I do to the NullProcessing property of this date attribute.

Is there any way to have SSAS display [NULL] (as in nothing at all) instead of "(blank)" ?

Creating a default member on date hierarchy



I am trying to set a default member on a date dimension.

I have an attribute form the database of IsToday which is a boolean with the value of today eg 23/11/2010 showing as true.

When i go into the Date dimension and try to select [Date].[Is Today].&[True] in Choose a member to be the default I get the error below

DefaultMember(Date,Is Today) (1, 1) The level '[Date]' object was not found in the cube when the string, [Date].[Is Today].&[True], was parsed.

Does anyone have any ideas why this is?


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