Click here to Skip to main content
14,603,917 members
Rate this:
Please Sign up or sign in to vote.
How to handle date on temp table when is null or not ?

I work on SQL server 2012 I using format dd/mm/yyyy

I need to handle date on #temp table on both of from date or to date .

if i have null on from date or to date what i do

if some one write format date is wrong How i make it as dd/mm/yyyy

What I have tried:

create table #temp
(
masterId  int,
fromdate datetime,
todate   datetime
)
insert into #temp values
(1,'12/01/2017','05/06/2019'),
(2,'12/02/2018','12/07/2019'),
(3,null,'12/09/2019')


select * from pcn.MasterData M inner join #temp tmp on m.MasterDataID = tmp.masterId
WHERE (tmp.fromdate is null OR tmp.fromdate >= M.InsertionDate ) AND (tmp.ToDate IS NULL OR tmp.ToDate <=M.InsertionDate)
Posted
Updated 24-Jan-20 14:41pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

This all depends on the context of how this data is to be used; and whatever business rules apply to it.

One of my projects has a need to pull documents for a specific date, and the data has from/to just as yours and there are some null values in there.
The business states that anything that has a null from date is good for all dates prior to the "to" date, and any null to dates are good for any time after the from date.
So the method used in my case is Coalesce (you could

My stored procedure looking for this is similar to this
DECLARE @DateDesired DATE = '04/15/2001'

SELECT  *
FROM    Documents
WHERE   @DateDesired >= Coalesce(FromDate, '01/01/1900')
AND     @DateDesired <= Coalesce(ToDate, '12/31/2500')
I hope this and the article below helps you determine how you will handle your situation.

Reference:
COALESCE (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
ahmed_sa 25-Jan-20 4:30am
   
I have insertiondate on table have formate dd/mm/yyyy

I need when user write any formate different from dd/mm/yyyy

like mm/dd/yyyy or yyyy/mm/dd

convert to dd/mm/yyyy

How to do that please ?
MadMyche 25-Jan-20 7:50am
   
What you need to understand is the difference between the "User Interface" and the underlying data. The DATETIME family of data-types are actually stored as numbers, and it is the UI that puts it into a human readable format.
It is up to the User Interface to display the DATE for the end-user, and it is to the application developer to know what format the data is coming in.

The most important thing about DATEs and TIMEs is to treat them as their proper data type, and DO NOT treat them as strings/varchars

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100