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 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

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 ?

thank you for reply and formate dd/mm/yyyy what select or number related to it SELECT CONVERT ( varchar , getdate(), ?????)

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

Answers

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

ahmed elbarbary

535

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 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)

Answers (3)

Next Recommended Forum

Strored procedure

sql server logical logs clean up