Assign a datetime value if a datetime parameter is null

how to assign null value to datetime in c#
how to insert null value in datetime field in c#
how to handle null datetime in c#
datetime null c#
how to pass null value to datetime in stored procedure c#
nullable datetime property in c#
convert datetime to nullable datetime c#
c# datetime null check

I have an SSRS report that has, among others, start and end date parameters. For both start and end date, an option NULL can be set. The rule is that if a start date is null then the data retrieved starts from the beginning of time. If the end date is null, then it is set to today's date. This is what I'm trying to do here:

SELECT * 
FROM mytab 
WHERE (Start >= CASE @From 
                   WHEN NULL 
                      THEN DATEADD(year, -100, @From)
                END) 
  AND (End <= CASE @To
                 WHEN NULL THEN GETDATE()
              END)

As a test, I declare variables @From and @To in my query, set them to NULL and run it on SSMS.

What I'm trying to achieve is to say if @From is null, set the value to 100 years ago and compare it to the mytab field 'Start' . Likewise, if @To is null set it to today's date and compare with 'End'. However this doesn't return any data. It's obviously wrong so what is the correct way to do this?

There's no need to use CASE statements here, as we can make use of ISNULL or COALESCE.

SELECT * 
FROM mytab 
WHERE Start >= ISNULL( @From , DATEADD(year, -100, GETDATE()) ) 
  AND End   <= ISNULL( @To   , GETDATE() )

DateTime "null" value, And, finally, there's a built in way to reference the default of any type. It is worth pointing out that, while a DateTime variable cannot be null , it still any NullErrorException and I can compare it to a date that I know isn't set. I have an SSRS report that has, among others, start and end date parameters. For both start and end date, an option NULL can be set. The rule is that if a start date is null then the data retrieved starts from the beginning of time. If the end date is null, then it is set to today's date. This is what I'm trying to do here:

The CASE syntax doesn't resolve the NULLs like you want.

DECLARE @TEST AS VARCHAR(10)

SET @TEST = NULL

SELECT CASE WHEN @TEST IS NULL THEN 1 ELSE 0 END AS TEST1, 
       CASE @TEST WHEN NULL THEN 1 ELSE 0 END AS TEST2

Result is 1 for TEST1 and 0 for TEST2 - not 1 and 1 as expected.

SELECT * from mytab WHERE (Start >= CASE WHEN @From IS NULL THEN DATEADD(year, -100, @From) END) 

AND (End <= CASE WHEN @To IS NULL THEN GETDATE() END)

How to set DateTime to null in C#, DateTime is a Value Type like int, double etc. so there is no way to assigned a null value. if(date == null) //WRONG. Value Type and Reference Type. c#  Yuck, I can see why that wouldn't be ideal. Ken and I were recently talking about the method in which we're doing updates - we should be able to see if there's a better way to do this.

A simple case expression when clause cannot match null, but you could test for it using is null in a searched case: case when @From is NULL then Cast( '1900-01-01' as Date ) else @From end. Note that an else clause supplies a value if none of the when clauses match.

A tidier solution for your issue is to use Coalesce:

select *
  from MyTab
  where Coalesce( @From, Cast( '1900-01-01' as Date ) ) <= [Start] and
    [End] <= Coalesce( @To, Cast( GetDate() as Date ) );

Note that the value is cast to a date to eliminate the time-of-day.

how to assign null value to datetime in C#.net, You define the datetime variable as nullable: Hide Copy Code. DateTime? dt = null;. This C# example program uses a nullable DateTime instance. Nullable DateTimes can be null. Nullable DateTime. A nullable DateTime can be null. The DateTime struct itself does not provide a null option. But the "DateTime?" nullable type allows you to assign the null literal to the DateTime type. It provides another level of indirection. Null

C# Nullable Datetime, Using the DateTime nullable type, you can assign the null literal to the DateTime type. A nullable DateTime is specified using the following question mark synta GetValueOrDefault()); } static void DateFunc(DateTime? dt) { if (dt.HasValue) { Console.WriteLine(dt.Value); } else { Console.WriteLine(0); } } }  Compute date and time values. Get relative dates and call DateTime constructors and properties. Each day the sun rises. Bright light marks a new day. A yellow bird searches for food. From Earth's surface we view the sun's path. What begins as motion gains meaning as time. A .NET developer uses DateTime—with TimeSpan and Stopwatch we

C# DateTime.MinValue (Null DateTime), Instead of null assignment, we can use the DateTime.MinValue field.Nullable​DateTime. This example checks the initial value of a DateTime member variable. Since DateTime is a Value Type (not a Reference Type), so you can't assign. null or Nothing to it. If you want to do something simelar, you could use. the MinValue property:

DateTime.TryParse Method (System), A span containing the characters representing the date and time to convert. When this method returns, contains the DateTime value equivalent to the date The conversion fails if the s parameter is null , is an empty string (""), or does not  Converting between DateTime and DateTimeOffset. 04/10/2017; 14 minutes to read +5; In this article. Although the DateTimeOffset structure provides a greater degree of time zone awareness than the DateTime structure, DateTime parameters are used more commonly in method calls.

Comments
  • Hi, please provide (1) Queries to CREATE your tables including indexes. (2) Queries to INSERT sample data. (3) The desired result given the sample, as text or image of excel for example. (4) A short description of the business rules, and how you got 1-2 of the results. (5) Which version of SQL Server you are using (this will help to fit the query to your version).
  • Have you tried using the CASE WHEN @From IS NULL THEN ... syntax?
  • You can simply coalesce values, e.g. Coalesce( @To, Cast( GetDate() as Date ) ).