date operations | SQLServer.info

Requires A Singleton?

November 8, 2017August 28, 2017 by robert

Have you ever got this error message when you tried to parse an XML string in SQL Server?

XQuery [value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’

DECLARE @XMLToParse XML

-- Load the XML data in to a variable to work with.

-- This would typically be passed as a parameter to a stored proc

SET @XMLToParse = '<Animal>

<AnimalName>Baboon</AnimalName>

</Animal>'

SELECT

@XMLToParse.value('data(/Animal/AnimalName)', 'VARCHAR(MAX)') AS AnimalName

How do we fix this?

This error is saying that the value() function needs an xpath that only exits once (non-repeating node). You and I know that there will only be one instance of , but SQL Server does not know that. To solve this, we just need to tell SQL Server that it should use the first instance of this AnimalName node that it finds. We do this by added the [1] tag to the xpath. We want to add it after the closing parentheses. This will tell SQL Server to grab whatever is the first node with that name.

DECLARE @XMLToParse XML

-- Load the XML data in to a variable to work with.

-- This would typically be passed as a parameter to a stored proc

SET @XMLToParse = '<Animal>

<AnimalName>Baboon</AnimalName>

</Animal>'

SELECT

@XMLToParse.value('data(/Animal/AnimalName)[1]', 'VARCHAR(MAX)') AS AnimalName

-- Notice the [1] on the line above!

Extra Fun

If there are multiple nodes with the same name in your XML, you can adjust the number inside the square braces to match whatever node you want. For example:

<AnimalName>Baboon</AnimalName>

</Animal>

[1] would return Baboon
[2] would return Cat
[3] would return Dog

How To Create A Date Table

November 12, 2014 by robert

Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.

CREATE

TABLE DateTable

(DateValue DATETIME)

DECLARE @StartDate DATETIME = '01/01/2014'

DECLARE @NumberOfDays INT = 3000

INSERT

INTO DateTable

(DateValue)

SELECT DATEADD(DAY, RowNum, @StartDate) AS DateValue

FROM (SELECT TOP (@NumberOfDays)

p1.PARAMETER_ID,

ROW_NUMBER() OVER (ORDER BY p1.PARAMETER_ID) - 1 AS RowNum

FROM sys.all_parameters p1

CROSS JOIN sys.all_parameters p2) NumberTable

SQL Server Quarter From Date

November 4, 2014October 22, 2014 by robert

One question that we get a lot is how to get the quarter from a date. SQL Server has a very easy way to get the quarter from a date (since SQL 2005) using the DATEPART command.

To get the quarter from the current date

1	SELECT DATEPART(quarter, GETDATE()) AS Quarter_From_Date

To get the quarter from a different date

DECLARE @DateToCheck DATETIME

SET @DateToCheck = '7/1/2014'

SELECT DATEPART(quarter, @DateToCheck) AS Quarter_From_Date

SQL Server Truncate Date

November 4, 2014October 17, 2014 by robert

A common function that people need to do when dealing with datetimes is extracting the date from the datetime. Oracle has a built-in function to do this called TRUNC. SQL Server does not have this (yet). However… you can accomplish this very easily. Here are a few ways to perform a truncate date function in SQL Server.

-- Assign date to variable

DECLARE @DateToTruncate DATETIME = '2014-08-01 14:12:34'

-- Get the date using casts

SELECT CAST(CAST(@DateToTruncate AS DATE) AS DATETIME)

-- Get the date using convert

SELECT CAST(CONVERT(VARCHAR(10), @DateToTruncate, 101) AS DATETIME)