Archive

Archive for April, 2010

SQL Server 2008 new datetime types, functions and the conversions between them

April 27, 2010 3 comments

 

Datetime Types in SQL Server 2005:

Lets start with a quick look at the existing date time types in SQL Server. The datetime and smalldatetime, these two types are well known to us, So we wont be spending much time here.

Data Type Representation Accuracy
smalldatetime YYYY-MM-DD hh:mm:ss 1 minute
datetime YYYY-MM-DD hh:mm:ss[.nnn] 0.00333 second

 

Though these types provide a whole lot of datetime functionality required for any database. The problem is that the date representation is always combined with the time in both the types and there is no way to represent the date only component or time only component in a database with out doing a lot of cast and convert.

New Datetime Types in SQL Server 2008

SQL Server 2008 has introduced four new datetime data types for the date and time representations in SQL Server Databases.   

Data Type Representation Accuracy
date YYYY-MM-DD 1 day
time[n] hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetime2[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetimeoffset[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 100 nanoseconds

 

The new Date type allows us to represents the date as an individual component with out the time field attached to it. And the Time type represents the Time as an individual component in databases. Note that the new Time type is at higher precision than the regular time in datetime type.

Datetime2 can be considered as an extension to regular datetime type with the time representation at an accuracy of 100 nanoseconds than at the regular 0.00333 seconds. The datetime2 type can be used with a default precision (datetime2(7)) or can be used with a user defined precision like datetime2(n) format.

If the time precision of datetime2 is set to 3 (datetime2(3)) which gives the exact equivalent representation of a regular datetime type.

DateTimeOffset is the combination of default datetime2 with the system time zone offset attached to it. The time zone offset displayed based on the Operating System date, time and culture settings. The offset should be between –14:00 to +14:00. The DateTimeOffset type does not support the day light saving times.

Here is my machine current datetimeoffset value 2010-04-25 23:24:07.7086518 +05:30. Notice that the time zone offset returned is +05:30 i.e. IST (Indian Standard Time) attached to datetime2 data.

New Date and Time Functions

SYSDATETIME() Similar to GETDATE() but it returns the current system date as new datetime2(7) type
SYSUTCDATETIME() Similar to GETUTCDATE() but it returns the current system date in UTC as a new datetime2(7) type
SYSDATETIMEOFFSET() Returns the current system date time as DateTime2(7) with system time zone offset attached to it
SWITCHOFFSET(expr, tz) Function to convert the datetimeoffset in one time zone to a datetimeoffset in another timezone
TODATETIMEOFFSET(expr, tz) Function to convert the datetime, datetime2 or datetimeoffset to a datetimeoffset with the specified time zone

 

Conversions between different datetime types

        1. How to convert datetime or datetime2 to datetimeoffset with specified time zone?

SELECT GETDATE(), TODATETIMEOFFSET(GETDATE(), '+08:30')
 -- results: 2010-04-26 00:18:36.927  | 2010-04-26 00:18:36.927 +08:30

SELECT SYSDATETIME(), TODATETIMEOFFSET(SYSDATETIME(), '+05:30')
-- results: 2010-04-26 00:18:36.9316407 | 2010-04-26 00:18:36.9316407 +05:30

TODATETIMEOFFSET() combines the specified datetime or datetime2 to the specified time zone offset.      
        
   
       2. How to convert the datetimeoffset in one time zone to datetimeoffset in other time zone? 

-- my current system time in IST (GMT + 5:30)
SELECT SYSDATETIMEOFFSET()
       -- 2010-04-26 00:32:48.1838343 +05:30       

-- IST (GMT + 5:30) converted to North America PST (GMT - 8:00)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00')
       -- 2010-04-25 11:02:48.1838343 -08:00

-- IST (GMT + 5:30) converted to Europe BST (GMT + 1:00)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+01:00')
       -- 2010-04-25 20:02:48.1838343 +01:00

      

       3. How to convert local datetime or datetime2 to UTC (Coordinated Universal Time)? 

DECLARE @LocalDateTime datetime
DECLARE @TimeZoneOffset INT

-- convert my current system datetime to UTC
SET @LocalDateTime = GETDATE()
     -- 2010-04-26 00:44:19.107

-- get the timezone offset of the system
SET @TimeZoneOffset = DATEDIFF(MI, SYSDATETIME(),SYSUTCDATETIME())
     -- 330 mins (i.e. 5:30 )

-- get the utc datetime
SELECT DATEADD(MI,@TimeZoneOffset, @LocalDateTime)
      -- 2010-04-25 19:14:19.107 

 

       4. How to convert datetimeoffset to UTC?

DECLARE @DTO DATETIMEOFFSET
-- convert my system datetimeoffset to UTC
SET @DTO = SYSDATETIMEOFFSET()
-- 2010-04-26 00:50:35.3701337 +05:30

-- switch offset to '+00:00' i.e. UTC
SELECT SWITCHOFFSET(@DTO, '+00:00')
      ,CAST(SWITCHOFFSET(@DTO, '+00:00') AS DATETIME2)
-- 2010-04-25 19:20:35.3701337 +00:00  | 2010-04-25 19:20:35.3701337

       5. Other simple conversions? 

SELECT CAST(GETDATE() AS Time)
      ,CAST(GETDATE() as date)
      ,CAST(SYSDATETIMEOFFSET() AS datetime2)
 -- results: 01:03:35.5700000 | 2010-04-26 | 2010-04-26 01:03:35.5971486

-- get timezone offset from datetimeoffset
SELECT DATEPART(TZ, SYSDATETIMEOFFSET())
 -- results: 330 (number of minutes)

 

The new date time types in SQL Server 2008 are more portable than datetime or smalldatetime and it is recommended to use them in the future development work.

Hope the post is helpful.

🙂 ranjith 🙂

Dynamically getting the execution status of a SQL Server Agent Job

April 23, 2010 2 comments

 

This problem seems trivial but there is no straight forward way to get this information. If you have tried the INSERT into EXEC command to insert the results of the sp_help_job procedure into a temporary table like below then you are familiar with the below error.

insert into #jobstatus
execute msdb..sp_help_job

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

So you can not directly insert the results to a temp table from this procedure. Here is a trick I have used using the OPENROWSET to get around with the INSERT EXEC problem. This query results can be stored into a temporary table or a table variable using the INSERT INTO or SELECT INTO command which can be used for further processing.

   select name, case when current_execution_status = 1 then ‘Executing’
           
when current_execution_status = 2 then ‘Waiting For Thread’
           
when current_execution_status = 3 then ‘Between Retries’
           
when current_execution_status = 4 then ‘Idle’
           
when current_execution_status = 5 then ‘Suspended’
           
when current_execution_status = 6 then ‘[Obsolete]’
           
when current_execution_status = 7 then ‘PerformingCompletionActions’
       
else NULL end as [status]  from
   openrowset
(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,‘EXEC msdb..sp_help_job’)
 

The OPENROWSET is one of the workarounds for the nested INSERT EXEC problem and many other solutions are proposed here (Thanks to Kalman for the workarounds).

Hope it helps

🙂 ranjith 🙂

SQL Server 2008 R2 Free eBook from Microsoft Press

April 22, 2010 Leave a comment

Just came to know about this book. More details on the Microsoft Press Blog.

You can download the ebook in XPS format here and in PDF format here.

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

Thanks,

🙂 Ranjith 🙂

Categories: SQL Server Tags: