Sql Server datetime timezone handling

SQL Server made modifications in 2005 onward where the internal timezone is saved in UTC. This was largely due to geo-replication and HA projectors involving log shipping, and having the log shipping times saved in different time zones made it impossible for the old method to restore them.
Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.
I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.

UTC is generally not very meaningful to a user.  You could theoretically convert the times to the end user’s local time zone.

A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field. This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.

These are the two most common requirements -
1. local time for local reports and
2. UTC time for group reports.

The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.

If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.

Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.

Example:

SELECT t_odat As 'Order Date stored in SQL Server', CONVERT(datetime,

               SWITCHOFFSET(CONVERT(datetimeoffset,

                                    t_odat),

                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))

       AS 'Order Date In LN'

from ttdsls401101

Production Planning

Bill Of Material: A (1) --- B(1). (A  - Manufactured item , B – Purchase item)

Routing: Operation for A/Unit – 1 Hour

Supply Time for B – 10 Days

Safety Time for B – 5 Days. (That means this item must be available 5 days in advance before start of the production).

Sales Order for A – 100 quantity – Planned Delivery Date – 30.01.2014

In the above case, after the planning run system gives two orders. 1) Planned production order for ‘A’ 2) Planned purchase order for ‘B’.

·         Planned Production Order Planned Finish date = Sales Order Planned delivery date. As there is no safety time mentioned for A, both the dates are same.

·         According to manufacturing time it will take around 4 days to complete the production of 100 units. SO the planned start date for A is 26.01.14.

·         As the production is starting on 26.01.14, the raw material must be available by that time. So the required date for B is 26.01.14.

·         But there is a safety time for 4 days. That means, the raw material must be available before its required date. So the planned finish date is 22.01.14. If at all there is safety time, the required date will be same as planned finish date.

BAAN/LN DLL to Print Amount in Words

tcmcs.dll0006.decode

void tcmcs.dll0006.decode( domain tcamnt amount, boolean decimals, ref domain tcmcs.s130m decode0 mb, ref domain tcmcs.st65m decode1 mb, ref domain tcmcs.st65m decode2 mb, ref domain tcmcs.st65m decode3 mb, domain tclang i.lang )

Expl.:
Pre: -
Post: -
Input: amount to be decoded, maximum 11+2
 decimals required (boolean)
Output:
 - decode0  Decoded amount of 130 chrs.
 - decode1  Same decoded amount in two strings of 65 chars
 - decode2  "
 - decode3  Decode of the decimals