January 24, 2020 at 10:49 pm
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
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)
January 25, 2020 at 12:40 am
if the fields were defined correctly as dates then the format does not matter
apart from that your code is doing what you asked for
January 25, 2020 at 9:31 am
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 ?
January 25, 2020 at 10:34 am
as I told you if you define the field as a datetime datatype the format you use does not matter - it is up to the UI to do the date validation and convert it into a datetime datatype before inserting onto the database.
how you display it on your application should depend on regional settings and display settings that your users have chosen
January 25, 2020 at 1:58 pm
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 ?
To insert into a column of type DATE in SQL Server there are (sort of) 2 official formats that work. Other formats might work too but it's probably a good idea to stick with the official design patterns. There are other SSC members who might strenuously disagree with this explanation. As Frederico has explained it's really best to have the data passed to SQL Server from the UI in the appropriate format. If you're determined to receive date data in those other formats, then you could store the user input in nvarchar(10) or nvarchar(12) and then run a prodecure or trigger to parse the input into a date using the DATEFROMPARTS (and other) function(s).
declare
@ttable(dt date not null);
/* acceptable format 1 */
insert @t values('20200101');
/* acceptable format 2 */
insert @t values('2020-01-01');
/* works but aren't recomended */
insert @t values('2020/01/01');
insert @t values('02/20/01');
select * from @t;
declare
@xtable(dt date not null);
/* doesn't work */
insert @x values('01012020');
select * from @x;
For my projects we use format #2 for everything.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 25, 2020 at 3:20 pm
There is no way to automatically distinguish differences between dd/mm/yyyy and mm/dd/yyyy for values of mm and dd that are less than 13 and so there is no way to automatically determine if the data has been entered correctly or not for validation during inserts into the table. It must be carefully controlled at the front end or whatever the data source is. This is why a lot of GUI's used pulldowns for Day, Month, and Year. It's still not a guarantee that no one will still do things backward but at least it ups the odds of correct entries.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply