New Date-Related Functions in SQL Server 2008

Along with the new data types DATE, TIME and DATETIME2 in SQL Server 2008, new and enhanced functions are also included.

These functions are:

• SYSDATETIME
• SYSUTCDATETIME
• SYSDATETIMEOFFSET
• SWITCHOFFSET
• TODATETIMEOFFSET
  

A more thorough explanation follows:

SYSDATETIME
------------------
This function is like the GETDATE function with the difference that it returns the current date and time with the precision of DATETIME2.


SYSUTCDATETIME
-----------------------
This function returns the current date and time in UTC again with the precision of DATETIME2.


SYSDATETIMEOFFSET
-------------------------
This function returns the current date and time along with the system time zone as a DATETIMEOFFSET value.


For all the above functions, if desirable, you can cast its values to DATE or TIME.


SWITCHOFFSET
------------------
This function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC vaslue. In plain words it returns the date and time along with the current time offset.


TODATETIMEOFFSET
------------------------
Even though it might seem similar to the SWITCHOFFSET function, the TODATETIMEOFFSET function has a different purpose.
It actually converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. So, if for an example you execute the following T-SQL statement:

SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET

you will get a DATETIMEOFFSET value saying that the date is: 2009-07-29 and time: 20:00 and the time zone offset is +03:00.

The exact result of the above T-SQL statement is: 2009-07-29 20:00:00.0000000 +03:00

Another difference between SWITCHOFFSET and TODATETIMEOFFSET is that the latter is not restricted to a DATETIMEOFFSET as an input value rather it accepts any date and time value (like in the above example).


------------------------------------------------------
T-SQL code examples using the above functions
------------------------------------------------------

--SYSDATETIME (Returns the System date and time with DATETIME2 precision)
SELECT GETDATE() AS CURRENT_DATETIME_OLD, SYSDATETIME() AS CURRENT_DATETIME_NEW
SELECT (CAST (GETDATE() AS DATE)) AS CURRENT_DATE_OLD, (CAST (SYSDATETIME() AS DATE)) AS CURRENT_DATE_NEW
SELECT (CAST (GETDATE() AS TIME)) AS CURRENT_TIME_OLD, (CAST (SYSDATETIME() AS TIME)) AS CURRENT_TIME_NEW

--SYSUTCDATETIME (Returns the UTC date and time with DATETIME2 precision)
SELECT SYSUTCDATETIME() AS CURRENT_UTC_DATETIME
SELECT (CAST (SYSUTCDATETIME() AS DATE)) AS CURRENT_UTC_DATE
SELECT (CAST (SYSUTCDATETIME() AS TIME)) AS CURRENT_UTC_TIME

--SYSDATETIMEOFFSET (Returns the Date and time along with the current time offset)
SELECT SYSDATETIMEOFFSET() as CURRENT_DATE_TIME_OFFSET

--SWITCHOFFSET (Adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET

--TODATETIMEOFFSET (Converts types that are not time zone aware to DATETIMEOFFSET by the given time zone offset)
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(GETDATE(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET(SYSDATETIME(), '+03:00') AS CURRENT_DATE_TIME_OFFSET
SELECT TODATETIMEOFFSET('2009-07-29 20:00', '+03:00') AS CURRENT_DATE_TIME_OFFSET
Read more on this article...

Live Webcast Announcement - SQL Server 2008 Programmability

One of the things that inspires me the most in SQL Server, is the continuous enhancement of its programmability features like Data Access (Entity Framework and Data Services support), Visual Studio Integration, LINQ, Sync Framework, Project "Velocity" and of course, T-SQL Enhancements.

So I think that it is time for a Live Webcast!

On Wednesday, July 29, 8:00 PM Cyprus Local Time (GMT+2), I will be delivering a public Live Meeting Webcast on SQL Server 2008 Programmability and more specifically on T-SQL Enhancements.

See below for event specific details:

-----------------------------------------------
Title: Microsoft SQL Server 2008 Programmability: T-SQL Enhancements
Type: Live Meeting Webcast
Language: English
Product: Microsoft SQL Server
Audience: Developer
Duration: 60 Minutes
Start Date: Wednesday, July 29, 2009 8:00 PM Cyprus Local Time (GMT+2)
Presenter: Artemakis Artemiou [Microsoft SQL Server MVP]
Level: 200-300
Registration (*Required): Click on this link.
-----------------------------------------------

* Note: The GMT offset above does not reflect daylight saving time adjustments.

The event was also announced on CDNUG's website.
The official announcement can be found here.

See you all online!
Read more on this article...

SQL Server 2008 Collation Alignment with Windows Server 2008

SQL Server 2008 is fully aligned with the collations in Windows Server 2008.
To this end, it has 80 new collations which are denoted by *_100 version references.

The total number of variations for these collations exceeds 1400.
The purpose of this enhancement is to provide users with the most up-to-date and linguistically accurate cultural sorting conventions.

In order to see the new collations along with their variations in SQL Server 2008 you can use the following T-SQL query:

--Get new collation details
SELECT [name], [description]
FROM sys.fn_helpcollations()
WHERE [name] LIKE '%100%'

For more information on SQL Server 2008 Collation and Unicode Support you can check out the following link.
Read more on this article...

Error converting data type varchar to float

You might get the above error message in different scenarios when trying to convert a string to a float.

The reason for getting this error message is that you are passing as a parameter to the cast or convert SQL Server functions, a varchar expression that is invalid.

Consider the following example:

-----------------------------------------
--Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.111,91'

--Perform the casting
SELECT Cast(@value AS FLOAT)

--or

--Perform the conversion
SELECT Convert(FLOAT,@value)
-----------------------------------------

If you execute the above code you will get an error message in the following type:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to float.

The exact reason for getting the error message in this case is that you are using the comma (,) as a decimal point and also the dots as group digit symbols.

Though SQL Server considers as a decimal point the dot (.). Also when converting a varchar to float you must not use any digit grouping symbols.

In order for the above code to execute you would need to first remove the dots (that is the digit grouping symbols in this case) and then replace the comma with a dot thus properly defining the decimal symbol for the varchar expression.

The code then can be modified as follows:

-----------------------------------------
--Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.111,91'

--Prepare the string for casting/conversion to float
SET @value = Replace(@value,'.','')
SET @value = Replace(@value,',','.')

--Perform the casting
SELECT Cast(@value AS FLOAT)

--or

--Perform the conversion
SELECT Convert(FLOAT,@value)
-----------------------------------------

If you execute the above code you will be able to get the string successfully converted to float.

I have also implemented the following function that can be used for the conversion of a string to float (by default I am using the size of 50 bytes for the varchar expression). Here it is the DDL code:

-----------------------------------------
CREATE FUNCTION [dbo].[Varchar2float]
(-- Input Parameter
@inputString VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
--Prepare the string for casting/conversion
SET @inputString = Replace(@inputString,'.','')
SET @inputString = Replace(@inputString,',','.')

--Perform the conversion and return the result
RETURN Cast(@inputString AS FLOAT)
END
-----------------------------------------

Function usage:

-----------------------------------------
--Variable declaration and initialization
DECLARE @value AS VARCHAR(50)

SET @value = '12.340.100,01'

--Call the funtion for performing the casting/conversion
SELECT dbo.Varchar2float(@value)
-----------------------------------------

*Note: Even though you can try changing the regional settings of the PC for setting the dot (.) as the decimal symbol this will only affect the way the data is presented to you when returned from the casting/conversion call. You still have to modify the varchar expression prior to the casting/conversion operation.


Read more on this article...

sp_MSforeachdb

A really neat undocumented SQL Server stored procedure is sp_MSforeachdb.
This stored procedure takes as parameters SQL commands which are then executed against all databases on the current SQL Server instance.

A simple example ("Hello World"-style :) is the following which lists all the databases in the current SQL Server instance:

exec sp_MSforeachdb
@command1="print '?'";

The stored procedure upon its execution replaces the question mark (?) with each database's name.

You can of course build more complex syntaxes like in the case where you just performed a side-by-side DBMS migration to SQL Server 2008 and you want to set the compatibility level for all the databases to 100 (SQL Server 2008) after of course you ensured that the databases are fully compatible with this version of SQL Server:

exec sp_MSforeachdb
@command1="print '?'",
@command2="ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 100";

*Note: Even though the stored procedure sp_MSforeachdb enables you to execute SQL commands against all the databases in a SQL Server instance, you must be extremely careful with the commands which you compose as this stored procedure access all the databases including the SQL Server system databases as well.

Also, I would not recommend putting something into a Production environment using the SQL Server undocumented stored procedures as one of the reasons for which they are undocumented, is that they might be removed at any time, as an example like in the case of a new SQL Server Service Pack or a new release.
Read more on this article...