DateTime and DateTime2
The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.
Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to
microseconds and avoids the 3/1000 second rounding issue.
The precision with a datetime2 is upto 100 nanoseconds.
DECLARE @dt AS DATETIME
SET @dt = GETDATE()
SELECT @dt
DECLARE @dt2 AS DATETIME2
SET @dt2 = GETDATE()
SELECT @dt2
As you can see, when using the datetime datatype is rounded to increments of .000, .003, or .007 seconds.
However the datetime2 has a larger date range, a larger default fractional precision, and optional
user-specified precision. The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds.
The default precision is 7 digits.
Moreover datetime2 supports a date range of 0001-01-01 through 9999-12-31 while the datetime type
only supports a date range of January 1, 1753, through December 31, 9999. The timerange as mentioned
earlier in case of datetime is 00:00:00 through 23:59:59.997
whereas in datetime2 is 00:00:00 through 23:59:59.9999999.
Friday, August 5, 2011
Stuff function in T SQL
The Stuff Function in SQL Server is used to deletes a sequence of characters from a source string and then inserts a string into another string.
Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.
Starting_Position: From which index position, you want to start inserting the New_String characters.
Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function will start at Starting_Position and delete the specified number of characters from the Character_Expression
New_String: New string you want to stuff inside the Character_Expression at Starting_Position
Syntax: SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)
FROM [Source]
DECLARE @Character_Expression varchar(50)
SET @Character_Expression = 'Learn Server'
-- Starting Position = 6 and End Position = 0
SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF'
-- Starting Position = 7 and End Position = 6
SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF'
-- Starting Position = 1 and End Position = 5
SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF'
-- Starting Position = 20 and End Position = 5
SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'
One more use of STUFF to get comma separated values in a a rows as per given below example.
DECLARE @TblCity TABLE(CountryCode INT, City VARCHAR(50))
INSERT @TblCity(CountryCode, City)
VALUES
(1, 'Johannesburg'),
(1, 'Cape Town'), --South Africa
(2, 'New York'),
(2, 'Washington'), --USA
(3, 'Paris'),
(3, 'Nice'), --France
(4, 'Rome'),
(4, 'Bologna'), --Itlay
(5, 'Athens'),
(5, 'Volos') --Greece
---// Add the Following SELECT FOR XML PATH Query to concatenate the Cities belonging to the same country code onto one line:
--Concat
SELECT DISTINCT CountryCode,
STUFF((SELECT ',' + City
FROM @TblCity t1
WHERE t1.CountryCode = t2.CountryCode
FOR XML PATH('')), 1, 1,'')
FROM @TblCity t2
Below is also an example to make comma separated values in a column.
But this STRING_AGG() function can be used in and above versions of SQL Server 2017
STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.
SELECT CountryCode, City = STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY CountryCode)
FROM @TblCity
GROUP BY CountryCode
As compare to FOR XML PATH query, STRING_AGG() function is much faster.. detail comparison given in below link.
Subscribe to:
Posts (Atom)