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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2675

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

    SSChampion

    Points: 14390

    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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2675

    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 ?

  • frederico_fonseca

    SSChampion

    Points: 14390

    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

  • scdecade

    SSChasing Mays

    Points: 654

    ahmed_elbarbary.2010 wrote:

    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
    @t table(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
    @x table(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

  • Jeff Moden

    SSC Guru

    Points: 995649

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply