How to handle date on temp table when is null or not ?

ahmed elbarbary

6y708

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

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

if i have null on fromdate or todate what i do

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

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)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
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 ?
ahmed elbarbary6y ago
thank you for reply
and formate dd/mm/yyyy what select or number related to it
SELECT CONVERT(varchar, getdate(), ?????)
Ankur Sharma6y ago
hi you can try these ways to reset date format in SQL server

To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)

or you can also do it from table menu manualy

right click on table=> column(time/date column) then you can edit or reset it