Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Date and Time in SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, October 26, 2007 1:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:13 AM
Points: 189, Visits: 34
Don't forget, if you run up against the limitations of the built-in types, you have the option of creating your own user-defined types.

Unfortunately, it does require CLR integration to be turned on and there is a performance hit.

See SQL Server 2005 Books Online
"CLR User-Defined Types"

Paste into IE (SQL2005 BOL installed):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/27c4889b-c543-47a8-a630-ad06804f92df.htm

(online version)
http://msdn2.microsoft.com/en-us/library/ms131120.aspx






Post #415263
Posted Friday, October 26, 2007 4:23 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Dear all, thank you for all your responses.

Jonathon Prosper: I'm not sure how datetimeoffset is stored Jonathon, but I'm guessing that 6-8 bytes are used to store the date and time elements (YYYY-MM-DD hh:mm:ss[.nnnnnnn]), whilst 2 bytes are used to store the timezone ([+|-]hh:mm). Whether it take 6, 7 or 8 bytes depends on the precision of the fractional second element (the .nnnnnnn), i.e. if the precision is 0-2 decimal points it is 6 bytes, if the precision is 3-4 decimal points it is 7 bytes and if the precision is 5-7 decimal points it is 8 bytes.

When a variable of datetimeoffset data type is implicitly converted to datetime data type, the result is the date and time components of the local datetime. For example, if the datetimeoffset is 2007-10-26 14:54:23.1234567 -06:00 then the datetime is 2007-10-26 20:54:23.123.

For explicit conversion we have the option of specifying the style. Style 0 means we want the output in local datetime (in the above example the output is 2007-10-26 20:54:23.123) whereas style 1 means that we want the output in UTC (in the above example the output is 2007-10-26 14:54:23.123)

Michel Stainer: Yes Michel you are right, it should be 31st December 9999. Thank you for correcting it.

BanzaiSi: Yes you are right BanzaiSi, the name of the function is CURRENT_TIMESTAMP, not CURRENT_DATETIME. Thank you for highlighting this mistake.

John Nolan: Please see below regarding datetime2 naming.

Nebojsa Ilic: Thank you for posting the storage size. See below regarding datetime2 naming.

Daniel Wolford: all the existing styles arguments for CONVERT function (0 to 131) work with the new data types. For example, to trim the nanoseconds part, we can do "convert (varchar, TIME, 108)" to produce hh:mm:ss or "convert (varchar, DATETIME2, 120)" to produce YYYY-MM-DD hh:mm:ss.

rswinehart: It seems that in 2008 ISDATE() would still be the only function that determines whether a variable is a valid date or time value. I agree that ISSMALLDATETIME

---------------------------------------------------------------------------

John Nolan, Nebojsa Illic, JJ B, Jamie Thompson, Joe Barbian and Ian Yates regarding DATETIME2 NAMING:

When I heard DATETIME2 I was also immediately thinking about Oracle's VARCHAR2. So I agree with you all that it's not a good name. I also agree that BIGDATETIME seems to be a more suitable name, as we hae SMALLDATETIME, SMALLINT, INT and BIGINT, as Jamie Thomson correctly mentioned.

On 21/8/2007 Eland Somarskog mentioned on Microsoft Connect (http://connect.microsoft.com) that datetime2 was not a very good name for a data type, for example when we needed to say "datetime2(4)". He mentioned BIGDATETIME and NEWDATETIME but prefers DATEANDTIME. The reasoning was BIGDATETIME is misleading as DATETIME2 may be smaller than DATETIME, and NEWDATETIME will not be new in 2020.

Microsoft's response was:
"The naming decision we made was mainly based on:
- demonstrating the transparency of the existing DATETIME type
- ensuring the new type name can be general enough to represent all new enhancements
Except for TIMESTAMP (ANSI SQL standard name for datetime), I should say we don’t see there is perfect name in the world about it. Unfortunately, TIMESTAMP is already taken for something else in SQL Server. After giving all the considerations, we believe that DATETIME2 is the best name against our guideline."

I think Jamie and Eland is right that hopefully we will have an alias for DATETIME2.

----------------------------------------------------------------------------

Jonathon Prosper, thanks for your explanation regarding Tibor Karaszi explanation about why 1753 is the earliest date for datetime. I found it very useful.
http://www.karaszi.com/sqlserver/info_datetime.asp#Why1753

Kind regards,
Vincent Rainardi
Post #415641
Posted Sunday, October 28, 2007 7:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 08, 2010 5:04 AM
Points: 135, Visits: 90
Jonathon Prosper, thanks for your explanation regarding Tibor Karaszi explanation about why 1753 is the earliest date for datetime. I found it very useful.


No problem!

Another question though, we have had some issues lately regarding jobs and daylight savings. The biggest issue has been the job scheduler not being able to tell the difference between a local time before daylight savings and the local time after daylight savings. This means that for jobs executing on a schedule frequency of less than one hour the jobs are ignored between 2am and 3am AFTER daylight savings takes effect as it believes the jobs have already been executed.

Does anyone know if the job scheduler will implement any of these new datetime datatypes specifically the datetime offset in 2008? I'm an unsure whether this issue was already resolved in 2005.


-- JP
Post #415815
Posted Thursday, November 01, 2007 5:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891, Visits: 1,958
Jonathon Prosper (10/28/2007)Another question though, we have had some issues lately regarding jobs and daylight savings. The biggest issue has been the job scheduler not being able to tell the difference between a local time before daylight savings and the local time after daylight savings. This means that for jobs executing on a schedule frequency of less than one hour the jobs are ignored between 2am and 3am AFTER daylight savings takes effect as it believes the jobs have already been executed.

Does anyone know if the job scheduler will implement any of these new datetime datatypes specifically the datetime offset in 2008? I'm an unsure whether this issue was already resolved in 2005.


I can't address the 2008 side of it specifically, but I saw a simple workaround. Let's say you have a job that runs at 15 minute intervals all day long. Schedule it to run from 0200-0100, then add three more schedule steps to run at 0115, 0130, and 0145. When the system clock backs up due to DST, you're still covered.
Post #417684
Posted Thursday, November 01, 2007 5:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 08, 2010 5:04 AM
Points: 135, Visits: 90
Thanks Wayne. Lets hope that 2008 deals with this issue without the need for these sorts of workarounds.

-- JP
Post #417690
Posted Thursday, November 01, 2007 5:20 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891, Visits: 1,958
I understand Novell doesn't have this problem, apparently they serialize their time so the clock knows that it's off in regards to jobs and it either speeds up or slows down slightly until it adjusts back to the correct time.

I'm glad that I don't work with Novell. :D
Post #417692
Posted Sunday, November 04, 2007 10:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188
Hi Jonathon, as mentioned on KB 931975, the DST issue still happens in SQL 2005, i.e. today (4th Nov) at 2 am the scheduled jobs were not running for 1 hour because the clock was wound back to 1 am so SQL job agent thought that the jobs had already been executed. Adding extra scheduled jobs as Wayne mentioned seems to be a good work around; thanks Wayne. I am not sure if this SQL Agent behavior is fixed SQL 2008 or not.
None of the date and time data type in SQL 2008 is DST aware. The datetimeoffset is time zone aware, but it is not DST aware. Perhaps this is because SQL Server gets the time information from Windows API.
Kind regards,
Vincent
Post #418314
Posted Sunday, November 04, 2007 8:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 7,082, Visits: 14,675
Wayne West (11/1/2007)
I understand Novell doesn't have this problem, apparently they serialize their time so the clock knows that it's off in regards to jobs and it either speeds up or slows down slightly until it adjusts back to the correct time.

I'm glad that I don't work with Novell. :D


Ah - but synthetic time can be VERY useful, since it allows you to keep everything sequenced appropriately.... What if you screwed up the server's date and time by, say - one full day when you set it up? And you mess up while you are traying to get the right time back into play....

After all - this wouldn't be the first technology that got "ported" or "adopted", or "renamed" (however you like to characterize features designed by Novell somehow appearing with new names and largely the same functionality in MS products....) Hmm let's see if we can remember a few...NDS (ADS) or ZenWorks (Microsoft SMS server)...


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #418364
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse