none
how should i handel datetime RRS feed

  • Question

  • Dear all

    i have a two servers, one is for asp.net app and other is for sqlserve

    1)how should i treat datetime in strored procedure  and in c# code please note i do not want to take risk(some body may change the format of date in windows or other places (except sqlserver internally))

    2) Please sugggest me for  windows  app which works on singel desktop where app and sqlserver both are on same machine and database can be on other machine also.

    yours sincerelly,


    • Edited by rajemessage Saturday, May 26, 2012 10:01 AM
    Saturday, May 26, 2012 9:46 AM

All replies

  • Hi 

    First of all it is not clear what environment you are using i.e. AD /standalone , for point one there are two suggestions 

    1. Always use UTC (Coordinated Universal Time) that is natively supported by all the applications and databases 
    2. If you are using SysDate make sure your system time is update . Usually in AD (domain) installation machines are in sync with AD clock , standalone machine can also be synced using automatic time update  feature)
    3. Do all your calculation based on UTC and and then handover to client in local time format 

    You question #2 is not very clear , can you please elaborate

    Ashwini

     

    Saturday, May 26, 2012 12:19 PM
  • Hi 

    First of all it is not clear what environment you are using i.e. AD /standalone , for point one there are two suggestions 

    1. Always use UTC (Coordinated Universal Time) that is natively supported by all the applications and databases 
    2. If you are using SysDate make sure your system time is update . Usually in AD (domain) installation machines are in sync with AD clock , standalone machine can also be synced using automatic time update  feature)
    3. Do all your calculation based on UTC and and then handover to client in local time format 

    You question #2 is not very clear , can you please elaborate

    Ashwini

    for 2) point. if some one changes date format of windows OS/system formte will it affect the code sqlserver/ c#.

     


    Sunday, May 27, 2012 5:16 AM
  • If by time format you mean like "mm:dd:yy" then answer is no because when any application like SQL Server or C#(CLR) query to operating system for time what all they got is a bignumber that and it is up to the rendering application how they want it to render on screen

    But if someone changes the time zone of local computer that is hosting SQL Server data base then it could be be a problem and because local time would be incorrect and by default SQL Server took system time zone setting to resolve local time, also by default hardware clocks (BIOS) are also configured to accept local time zone 

    as a workaround what I would suggest to use UTC time in your data base fields and in queries as well ( GetUTCDate() ) and this is universally accepted as well 

    Also I would suggest to use datetimeoffset as datatype in column definition because it has time zone awareness and is based on a 24-hour clock.

    Ashwini



    • Edited by Ashwini47 Sunday, May 27, 2012 6:45 AM
    Sunday, May 27, 2012 6:39 AM
  •  Please try to use datetime in sql server stored procedure(ie better to maintains)

    Ans 2: Windows application can be works on both if SQL server is in the same machine or in different machine.


    With Thanks and Regards Sambath Raj.C

    • Proposed as answer by Sambath Raj.C Monday, May 28, 2012 8:49 AM
    • Unproposed as answer by Sambath Raj.C Monday, May 28, 2012 8:49 AM
    Monday, May 28, 2012 8:49 AM