Handling A NULL Datetime In DATEDIFF()

 I want to select records where AnswerDate is within 30 days of today, and include records that have a NULL AnswerDate.

How can I tell DATEDIFF to treat NULL as Today?  (I think I am looking for something like VBA's nz() function.) 


SELECT RegistrationID ,  


(DATEDIFF(dd,AnswerDate,GETUTCDATE())) AS InvitationAge

FROM Registration

WHERE (DATEDIFF(dd,AnswerDate,GETUTCDATE())) <= 30




Handling A Null Datetime Column

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

View Replies View Related

DateTime Using DateDiff

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @PurchaseTotal for the year, I have to write the following:

@PurchaseTotal = Sum(PurchaseTotal)
DateDiff(yyyy, DateOf, @IntervalDate) = @Interval

To find @PurchaseTotal for a month, I have to write the following:

@PurchaseTotal = Sum(PurchaseTotal)
DateDiff(mm, DateOf, @IntervalDate) = @Interval

To find @PurchaseTotal for all the records, I have to write the following:

@PurchaseTotal = Sum(PurchaseTotal)

I've tried the following code but I get an error.

DateDiff(@DateParameter, DateOf, @IntervalDate) = @Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.


View Replies View Related

Datediff Formula On Insert Returning Null Value

I have a form with two date fields that the user will submit their requested vacation time off with.  When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table.  Everything inserts okay, but I am getting null for the request_duration.  If I put dates in quotes and run the query it comes back with the right results.  If I put the dates in the form and submit it, I get Null for the request_duration.  Thank you in advnace for any help on this!  INSERTrequest
1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date),

View Replies View Related

Handling Null Dates

Hi, I have a database field for completion dates - until a task is completed, there is no date and at the moment I have null values in this field.My problem arises when searching the records.  I have a search form which passes parameters via a query string to a SqlDataSource.  The SqlDataSource has the CancelSelectOnNullParameter set to true, so if any fields on the search form are left blank, they are ignored.  For other dates, my sql query contains something like  (Job.EnteredAt < ISNULL(DATEADD(d, 1, @EntTo), '2099-01-01')) This approach doesn't return any records for the completion date as there is no date to compare to the '2099-01-01'.Can anyone give me any tips on how I should handle this?  I'm willing to change my structure, search page or sql query!Thanks, Neil 

View Replies View Related

Handling NULL Values.


I would like to know the solution for the below problem.

Actually I have a single Hibernate xml file which has the query on it. And I am accessing the xml file for SQL server 2005 and Oracle 10G. But the query is working in Oracle and not working in SQl Server.

I noticed from the query that handling 'NULL' is the problem.

1. The following query is the same in both Oracle and SQL Server, which is causing the Issue.
WHERE o.OU_ID = 20000000

Oracle treats NULL and the empty string almost the same making the latter almost an alias for NULL
The following two statements produce identical results:
1. update employee set lastname = ''
2. update employee set lastname = NULL
In our case, the CLIENT_SKU_END_CODE is an empty string which is being checked with an empty string returns False in Oracle where as returns True in Sql Server causing the issue.

It would be great if any one who can give some possible solution to resolve the same.


View Replies View Related

Handling Null Date Values

I would like some suggestions on how to keep from displaying the value "1/1/1900" on my asp pages when my recordset field returns a Sql date value that is null

Thanks for any tips

View Replies View Related

Null Value Handling In SSIS Package

Hi All

I am facing this problem while loading data from text file into Table.

Scenario is -

There are chances of having spaces for null values in text file.

when i m trying to rum my SSIS package this is getting failed.

How can i avoid this problem? i want null values to be inserted if ther is spaces for that field in text file.



View Replies View Related

Handling Null Values In Listboxes In ASP.NET 2.0

I cannot be the only person who has encountered this problem.

We have a standard N-tier architecture -- custom BLL and strongly typed datasets for my DAL. The DB is the Northwind database (for testing). The datasource objectson the page utilize the BLL (using the TypeName property), and a strongly typed data transfer object (using the DataObjectTypeName property). These properties are set in-line in the page. The form includes several text fields and a DropDownList. Here are the names of the relevent components:

DataTypeObject - ProductRecord class (just a constructor and properties to store data values)
BLL - ProductsEditor class (data methods accept and return ProductRecord)
Main DataSource - ProductDatasource (talks only to BLL)
DropDownList Datasource - CategoryDatasource (dataset retrieved from BLL)
DropDownList - CategoryList (bound to the GroupDataSource object)

The CategoryDataSource retrieves a standard key/value dataset (<int32>, <string>) from the BLL. This source is used to populate the CategoryList drop-down, and the SelectedValue of CategoryList is bound to a <Nullable int32> property of the ProductRecord (via the ActionItemDatasource.) Since the field is Nulable, CategoryList has AppendDataBoundFieldItems set to True, and contains a single blank item written into the page '<asp:ListItem Value="" />'.

Basicaly, the design is straight out of the "how Microsoft says to do N-tier in .NET 2.0" box. Here's the behavior:

The page loads it's data just fine. If the user fills in all fields, it even saves the data just fine. However, when the user leaves the CategoryList drop-down blank, the page blows up: "Index was outside the bounds of the array". The actual exception text is "[Exception: is not a valid value for Int32.]"

It's clear that the problem stems from the fact that the the value of the selected item is an empty string. Since the field is bound to an int32? data element, that causes it to blow up. However, the databinding routines are supposed to take care of converting that empty string to a null value, which would be acceptable to an int32? property.

Based on additional troubleshooting, I determined that the "empty-string-to-null" auto-conversion stops working when I inject a strongly typed data transfer object (ProductRecord) into the mix. It works fine if I'm using a straight dataset as the transfer object, but the minute I try to use something else, the auto-conversion of nulls blows up.

I haven't been able to find anything online about this, and yet the problem is so basic I can't imagine it's particularly rare.

Any pointers from anyone here?

View Replies View Related

Handling Null Fields With Three-tier Architecture

I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database.   I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture.  I have been following the principles of Scott Mitchell's tutorials.  I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor.   Thus where the first version had code: -
        Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'"        Dim message As String = ""        Dim Found As Boolean = False        Try            Using connection As New SqlConnection(GDB_AppSettings.Connection)                Dim command As New SqlCommand(queryString, connection)                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                If reader.Read() Then                    Found = True                    _USRid = reader("USRid")                    _USRPassword = reader("USRPassword")                    _USREmail = reader("USREmail")                    _USRTitle = reader("USRTitle")                    _USRName = reader("USRName")                    _USRRole = reader("USRRole")                    If IsDBNull(reader("USRAgreedConditions")) = False Then                        _USRAgreedConditions = reader("USRAgreedConditions")                    End If                End If                reader.Close()            End Using        Catch ex As Exception            If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then                Return "Cannot open database to logon"            Else                Return Err.Description            End If        End Try
the new version is much more elegant: -
        Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
        Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid)        If tbUser.Count <> 1 Then   '   Anything other than 0 or 1 should be impossible            Return "User not found"        End If
        Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0)        _USRid = userid        _USRPassword = password        _USREmail = trUser.USREmail        _USRTitle = trUser.USRTitle        _USRName = trUser.USRName        _USRRole = trUser.USRRole        _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem.  The database field USRAgreedConditions is a Datetime field that can be null.  The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code  Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull."  Source="App_Code.wt2vzoc1"  ....
There is no point in writing: -        If Not IsDBNull(trUser.USRAgreedConditions) Then            _USRAgreedConditions = trUser.USRAgreedConditions        End Ifbecause the exception occurs within the automatically-created code in the data access layer.  I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String.  Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1.   To stick with the old query-building code.   But this doesn't give me the advantages of a proper 3-tier architecture2.   To change the generated code in wt2vzoc.  This MUST be a bad idea - surely I should leave this code untouched.  Besides, what if the table adaptor has to be regenerated when I change the table design?3.   Code a Try block within the business layer: -    Try         _USRAgreedConditions = trUser.USRAgreedConditions    Catch ex As Exception         _USRAgreedConditions = Nothing    End Try
This seems to work OK, but seems less elegant than the original code in the old version: -       If IsDBNull(reader("USRAgreedConditions")) = False Then            _USRAgreedConditions = reader("USRAgreedConditions")       End IfIs there a better way?

View Replies View Related

Insert NULL Value Into DateTime

Does anyone have a simple way of inserting a NULL value into sql 2000 datetime. I'm using vb.net.
All I want is if the user does not enter a date in a textbox to to send a NULL value to the DB instead of having the db enter the default value as 1/1/1900.
Thank you

View Replies View Related

Null Datetime Field

Hi, I am trying to load data into a smalldatetime field from a flatfile. The data that looks like this '20001201' I have no problem converting to a smalldatetime field. However, sometimes there is no date, the date looks like this '00000000'. When I try and convert this to a smalldatetime field I get an out of range error message. Is there any way to have a date field that looks like this 0000-00-00?

View Replies View Related

INSERT NULL Value + DateTime


If a datetime variable is empty I want to insert null into the database table.

How do i do this? Below code doesn't work. Get this error...
Cannot implicitly convert type 'System.DBNull' to 'System.DateTime'

Code Block
if (myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString() != "")
myDateReport = DateTime.Parse(myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString());
myDateReport = DBNull.Value;

View Replies View Related

Set NULL To Datetime Parameter

Hi All,

I have a problem while trying to set"NULL" valueto my datetime parameters.

I have two datetime parameters (AllowNull=True)on my report. (BeginDate and EndDate)

it works fine in preview mode in reporting design.

The problem occurs, if I try to set an emtpy value to these parameters in my asp.net application.

The code looks like;

Dim repParams(1) As ReportParameter

repParams(0) = New ReportParameter("BeginDate", String.Empty)

repParams(1) = New ReportParameter("EndDate", String.Empty)


ReportParameter method requires String parameters. If user set any datetime value, I am sending "String.Empty" valueto myparamaters in order to set them as NULL. But I am getting"rsReportParameterTypeMismatch Error."

How Can I set NULL value to these parameters from asp.net application?

Thans alot in advance.

View Replies View Related

DateTime Parameter With NULL Value

I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).

I used datetime parameter (to have the calendar control for selecting date) for which I enabled NULL value. The result is that next to this parameter there is a checkbox "NULL" which allows to send NULL as a value of this parameter. Everything is fine except for the prompt: "NULL"... Is there a way to change it? I would see there something like "Empty" or "Not selected", which would be understandable for users of the report.

Or is there any other way to send an empty date as a value of this parameter? (Setting some unusual value like 1/1/1900 for empty date is not sufficient as the empty value shouldn't appear in the parameter control).

Thank you,

View Replies View Related

Xsd Files And Null Datetime Columns

Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns.  I need to allow NULL values to be saved, but when I try to do that I get an error
In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error
cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'
I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ?
In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property
I could probably do this another way (by not using a schema and writing the code myself)  but Id prefer to keep the strongly typed code.
 I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?

View Replies View Related

Inserting NULL Into Datetime Field

I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value?), so I don’t know how I can insert this value when the field is blank? 
Can anyone shed some light please? Thanks

View Replies View Related

Cant Insert NULL To DateTime Field

Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty.
I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime.
If I don't set null value as above, some large date (01/01/2033) has been inserted in my database.
Can anyone help me?

View Replies View Related

DateTime Null In Sql Server Database

I'm using this source code in order to set the DateTime field of my Sql Server database to null.I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.
I have an error saying that 'object' does not contain definition for 'Value'.
In french : Message d'erreur du compilateur: CS0117: 'object' ne contient pas de définition pour 'Value'dbCommand.Parameters["@DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;
The funny thing is that in the class browser i can see the Value property for the class Object...
C#, asp.netstring  sqlStmt ; string  conString ; SqlConnection cn =null; SqlCommand cmd =null; SqlDateTime sqldatenull ; try{sqlStmt = "insert into Emp (Date) Values (@Date)  ";conString = "server=localhost;database=Northwind;uid=sa;pwd=;";cn = new SqlConnection(conString);cmd = new SqlCommand(sqlStmt, cn); cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));sqldatenull = System.Data.SqlTypes.SqlDateTime.Null; if (myDate == DateTime.MinValue) {cmd.Parameters ["@Date"].Value =sqldatenull ;}  else{cmd.Parameters["@Date"].Value = myDate;}cn.Open();cmd.ExecuteNonQuery();Label1.Text = "Record Inserted Succesfully";}catch (Exception ex){Label1.Text = ex.Message;}finally{cn.Close();}

View Replies View Related

Null Datetime Field Contains 1-Jan-1900

I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset.
How do I get rid of this?


View Replies View Related

Null Datetime Value Problem In Sproc

Hi. I wrote a sproc to insert values from a tab-delimited text file into SQL 2005. There is a column in the text file that contains datetime values. Some of the values may be null. I have defined my sproc as:[CODE]createproc [dbo].[spInsertTransactionData]@LoadTransactionCode tinyint,@AccountNumber char(19),@PostingDate char(8) = '01011900'asinsert into table1( LoadTransactionCode, AccountNumber, PostingDate)values(@LoadTransactionCode,@AccountNumber,cast(stuff(stuff(@PostingDate, 3, 0, '/'), 6, 0, '/') as datetime)) [/CODE]I am getting the error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."When I step into the sproc, I see that the problem arises when the code encounters a "blank" value for PostingDate.As always, thanks for the help!

View Replies View Related

How To Install A Null Value Into A DateTime Column Via A TableAdapter?

Hi,I have a table adapter that I am using to call a stored procedure on a SQL server database to insert a record.Everything works ok, except I cannot figure out how to pass a null value to a DateTime field.  In my SQL server database, the column allow nulls.  In my typed dataset, the column is set to allow nulls.When I try to run the insert procedure with a null value: ListTableAdapter da = new ListTableAdapter();
da.InsertList(System.Data.SqlTypes.SqlDateTime.Null, Name); I get this error: Error    14    Argument '1': cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'System.DateTime?I've also tried passing it DBNull.Value, but I get an exception with that too.Can anyone steer me in the right direction with this?

View Replies View Related

Sql SELECT Returning NULL On DateTime Field

Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where  Date1='8/9/2006 11:17:41 AM'
2)select * from X where  Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance

View Replies View Related

How Do You Handle Null DateTime Values From SQL Server?

If myDateTimeColumn contains a <NULL> value.  How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?

View Replies View Related

Null Values In Datetime Fields, Howto?

When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?

View Replies View Related

Insert NULL Into Datetime Datatype Field

I need insert Null to datetime datatype field. The code is following:

strDate = "NULL"

sqlString= "UPDATE job SET " & _
"posting_date='" & REPLACE(strDate, "'", "''") & "', " & _
"pending= '" & REPLACE(strPending, "'", "''") & "'" & _
"WHERE job_id= " & strJobID

In the posting_date filed alread has 7/16/2001 I need update field to <NULL>
I write the the code but it didn't work Please tell me how correct update or insert NULL to the datetime datatype field in SQL Server Database.
Please help me and thanks Scott answer me the select question.

View Replies View Related

Problem With BCP Near Null Values In Datetime Fields


I use DTS packages to BCP large data files into SQL tables. In these packages I describe where the BCP - and the format file are saved.

Now the problem:

In these BCP files there are fields which contained datetime values. Sometimes these fields are empty and they have the NULL value. After the BCP procedure the NULL fields contains real date values which is not correct. If there is a date field which contained a real date value and the next record contains a null value, this field becomes the date value from the record above???

In SQL 7.0 was this not a problem. Can anyone help me?

Thanks in advance

Michel Landman

View Replies View Related

Sending Null DateTime Value To Stored Procedure From VB

In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)

The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,

However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.

I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.

I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?


View Replies View Related

Data Conversion - NULL Datetime Causes Failure


I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.

When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an erroroutput. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)

This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug??? It seems to me it is. I can't believe this would be the desired behavior?!

Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?


View Replies View Related

How To Replace DateTime Field With Null Value In SQL 2005 Server

How to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa  @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate

View Replies View Related

Mssql Won't Find NULL Values In Datetime Field??


I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:


I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:


View Replies View Related

Conditional Execution With Null Checking For DateTime Field

There was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?

My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields totheir respective global variables.

Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'

I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...

Could you enlighten me, please???
Thanks in advance!

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved