Handle Date Time with Null Values

_{Tuesday, January 20, 2015 10:06 AM}

Hi i have a column CreatedDate (DateTime)

Declare @dateFrom DATETIME=NULL,
@dateTo DATETIME=NULL

SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19'

Select * from user where  user.createdDate BETWEEN CAST(@dateFrom as DATE) AND CAST(@dateTo as DATE)

Now its giving me two rows thats fine..

Lets say i am not passing dateField means  @dateFrom and @dateTo is null 

Then to handle null i am writing like this

Select * from user where usr.createdDate BETWEEN ISNULL(CAST(@dateFrom as DATE),'1900-01-01') AND ISNULL(CAST(@dateTo as DATE),'1900-01-01')

Then its not giving any rows as it should give those two rows.

How do i handle null for dateTime.

Thanks in advance

Niki

_{Tuesday, January 20, 2015 10:46 AM ✅Answered}

This should work

SELECT @dateFrom=isnull(@dateFrom,'19000101');
SELECT @dateTo=isnull(@dateTo,'20991231');  Or GetDate()


SELECT * from dbo.users  u
WHERE u.createdDate BETWEEN @dateFrom AND @dateTo AND
(type=3 and Verify=@verify); 

Satheesh
My Blog | How to ask questions in technical forum

_{Tuesday, January 20, 2015 10:18 AM}

I am not sure I understood your question, Please revert incase of you are expecting something else:

Try the below:

Select *  from users where  (users.createdDate BETWEEN CAST(@dateFrom as DATE) AND CAST(@dateTo as DATE) and Coalesce(@dateFrom,@dateto) is not NULL)
             Or (Coalesce(@datefrom,@dateTo) is null)

_{Tuesday, January 20, 2015 10:19 AM}

Select * from user where usr.createdDate >=coalesce(cast(@dateFrom as date),'19000101') and

usr.createdDate<=coalesce(cast(@dateTo as date),'19000101')

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Tuesday, January 20, 2015 10:24 AM}

Hi Niki, it looks like it just might be a matter of updating your @dateTo ISNULL replacement value.  As it is now, you're using the same date (Jan. 1, 1900) for the @dateTo and @dateFrom values.

Thanks,
Sam Lester (MSFT)

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

_{Tuesday, January 20, 2015 10:26 AM}

try below

i think you could try by extending the @dateto to very last date like '9999-12-31' instead of '1900-01-01'

Declare @dateFrom DATE=NULL,@dateTo DATE=NULL

SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19'

    Select * from user where 
    usr.createdDate 
      BETWEEN 
        ISNULL(@dateFrom ,'1900-01-01') 
        AND 
        ISNULL(@dateTo ,'9999-12-31')

Thanks
Saraavana Kumar C

_{Tuesday, January 20, 2015 10:27 AM}

Try

Select * from user where usr.createdDate BETWEEN ISNULL(CAST(@dateFrom as DATEtime),CAST('1900-01-01' as DATEtime)) AND ISNULL(CAST(@dateTo as DATE),CAST('1900-01-01' as DATEtime))

Many Thanks & Best Regards, Hua Min

_{Tuesday, January 20, 2015 10:38 AM}

one thing i have noted is, you are casting the parameter as DATE while the column comparing against is DATETIME,i think better you can add timestamp to get accurate result

please refer below script

GO
Create table #users
(
id int identity(1,1),
createdDate datetime
)
GO
insert into #users(createdDate)
select '2015-01-07' union all
select '2015-01-19' union all
select '2015-01-19 05:30:13.580'


Declare @dateFrom DATETIME=NULL,@dateTo DATETIME=NULL
--For Null parameter
--SET @dateFrom= '2015-01-07'
--SET @dateTo='2015-01-19 23:59:59.000'


    Select * from #users where 
    #users.createdDate 
      BETWEEN 
        ISNULL(@dateFrom ,'1900-01-01') 
        AND 
        ISNULL(@dateTo ,'9999-12-31')
--parameter Without timestamp       
SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19'


    Select * from #users where 
    #users.createdDate 
      BETWEEN 
        ISNULL(@dateFrom ,'1900-01-01') 
        AND 
        ISNULL(@dateTo ,'9999-12-31')
--parameter With end timestamp
SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19 23:59:59.000'


    Select * from #users where 
    #users.createdDate 
      BETWEEN 
        ISNULL(@dateFrom ,'1900-01-01') 
        AND 
        ISNULL(@dateTo ,'9999-12-31')
    

_{Tuesday, January 20, 2015 10:39 AM}

Thanks for reply but thats not working. Let me tell you in more clear way

This is the Query

Now when adding this 

users.createdDate BETWEEN CAST(@dateFrom as DATE) AND CAST(@dateTo as DATE) and Coalesce(@dateFrom,@dateto) is not NULL)
             Or (Coalesce(@datefrom,@dateTo) is null)it is giving me all the data for usertype 3,4,5 and all..As it should give only usertype=3

Means by adding your lines it handles null but when i am setting

SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19'

then its giving data for all userType i.e 3,4,5

_{Tuesday, January 20, 2015 10:41 AM}

>>Lets say i am not passing dateField means  @dateFrom and @dateTo is null 

What is the meaning of @DateTo being NULL? I don't think you need 1900-01-01 as @EndDate, rather what you may need would be 2099-12-31 or even getdate().

Declare @dateFrom DATETIME=NULL,@dateTo DATETIME=NULL;

SET @dateFrom= NULL;
SET @dateTo=NULL;

SELECT @dateFrom=isnull(@dateFrom,'19000101');
SELECT @dateTo=isnull(@dateTo,'20991231');  Or GetDate()


SELECT * from dbo.users  u
WHERE u.createdDate BETWEEN @dateFrom AND @dateTo; 

Satheesh
My Blog | How to ask questions in technical forum

_{Tuesday, January 20, 2015 10:42 AM}

Try the below:

Create Table users(createddate date, usertype smallint)
Insert into users Values('2015-01-07',3),('2015-01-19',3),('2015-01-18',2)

Declare @dateFrom DATETIME=NULL,
@dateTo DATETIME=NULL, @isVerify smallint = 0

SET @isverify = 3

SET @dateFrom= '2015-01-07'
SET @dateTo='2015-01-19'

Select *  from users where  ((users.createdDate BETWEEN CAST(@dateFrom as DATE) AND CAST(@dateTo as DATE) and Coalesce(@dateFrom,@dateto) is not NULL)
             Or (Coalesce(@datefrom,@dateTo) is null)) And usertype=@isVerify

Drop table users

_{Tuesday, January 20, 2015 11:01 AM}

Perfect...  Thanks.

That works

Niki