T-SQL - Converting Time in SQL Server to Ticks

We were trying to determine why the start date field in a tool we use was stored as a bigint. I thought it may be ticks.

To figure this out we tried to do the following. It did not get our answer, but it was a very cool use of the binary data type.

DECLARE @date datetime = '1/22/2013'

,@ticksPerDay BIGINT = 864000000000declare @date2 datetime2 = @datedeclare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9))declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks]


Detailed reference.
http://stackoverflow.com/questions/7386634/convert-sql-server-datetime-object-to-bigint-net-ticks

Turned out the value was a json formatted date, which is defined as number of milliseconds in Universal Coordinated Time (UTC) since midnight January 1, 1970 (UTC). Again, this is a mere convention and not part of the JSON standard. If you are exchanging data with another application, you will need to check its documentation to see how it encodes date and time values within a JSON literal. This is based on the face that javascript does not have a datetime datatype, so you can pass the time as a big int (which is what we had) or a string that is formatted like a date. The below test transform targets the date

DECLARE @EpochDate
DateTime2 = '1/1/1970',@TargetDate DateTime2
--change this date for your transformation,@JsonDate BIGINT = 628318530718
,@Milli INT,@Seconds BIGINT
SET @Milli = RIGHT(@JsonDate,3)SET @Seconds = LEFT(@JsonDate,LEN(@JsonDate)-3)SELECT @EpochDate, @JsonDate, @Milli, @Seconds SET @TargetDate = dateadd(ms, @Milli,dateadd(second,@Seconds,@EpochDate))IF ((MONTH(@TargetDate) >= 3 AND DAY(@TargetDate) >= 11 AND HOUR(@TargetDate)AND
(
MONTH(@TargetDate) <= 11 AND DAY(@TargetDate) <= 2))SELECT DATEADD(HOUR,-8, @TargetDate)ELSE SELECT DATEADD(HOUR,-7, @TargetDate)

T-SQL MERGE INTO With Replace Substring Manipulation - When You are Thinking About things the Hard Way

Today I was told that we needed to change the ULRs for some sites to remove the subdomain element of the URL and instead insert an environment value before the domain. 

I opted to do a MERGE INTO rather than a simple update. The reasons for this are technical and complicated. Well really, I had never used REPLACE in an UPDATE and assumed it would not work. I also was more interested in something fancy, rather than the simple solution. Oops.

So instead of doing this

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

UPDATE PartnerConfig USING (
        SET ConfigString = REPLACE(ConfigString,@prodDomainStr, @qaDomainStr)
WHERE ConfigTypeID = @configType


I used this. Both did the job. It is an ad hoc infrequent update of a limited record set (<100 rows). It took me longer to write the merge, and I am sure the query plan is worse for me doing so. Lesson learned.

USE Database123
DECLARE
    @prodDomainStr char(19) = '.Company.com',
    @qaDomainStr char(19) = 'UAT.Company.com',
    @configType INT = (SELECT ConfigTypeID FROM Config WHERE NAME = 'URLInBound')

MERGE INTO PartnerConfig CCTarget
USING (
        SELECT PartnerConfigID, REPLACE(ConfigString,@prodDomainStr, @qaDomainStr) VALUE
        FROM PartnerConfig
        WHERE ConfigTypeID = @configType
      ) AS CCSource
        ON CCTarget.PartnerConfigID = CCSource.PartnerConfig
WHEN MATCHED THEN
UPDATE
SET CCTarget.ConfigString = CCSource.ConfigString;

As always, the example has been sanitized to use generic names. This can lead to malformed code.

T-SQL - XQuery - Delete Nodes From XML Document

When working with XML in SQL server you are using a sublanguage called XQuery inside of T-SQL. You have access to XML methods when using an XML typed value.  This query was written to be run by support, so it has some built in tests that assist support in knowing we are targeting the correct data. The keys here are:
Practice this in a test environment before you do it on prod. XML is tricky.

Understand the layout of your XML document. The one I was targeting was simple. We have some duplicate data and I wan to remove it.

<ResponseRoot attrib1='1' attrib2='2'>
<Answer id='77' value='work it out'>32</Answer>
<Answer id='78' value='work it out'>22</Answer>
<Answer id='79' value='work it out'>1112</Answer>
<Answer id='80' value='work it out'>112</Answer>

<Answer id='81' value='work it out'>12</Answer>
<Answer id='82' value='work it out'>42</Answer>
<Answer id='83' value='work it out'>552</Answer>
<Answer id='84' value='work it out'>92</Answer>
<Answer id='85' value='work it out'>2</Answer>

<Answer id='86' value='work it out'>12</Answer>
<Answer id='87' value='work it out'>42</Answer>
<Answer id='88' value='work it out'>552</Answer>
<Answer id='89' value='work it out'>92</Answer>
<Answer id='90' value='work it out'>201</Answer>


</ResponseRoot>

The [13] is part of XPath, which is used by XQuery to locate items in an XML document. Unlike C# (which enumerates nodes from 0), XQuery in T-SQL enumerates the nodes from 1.  Below I am looking for the 9th through 13th child nodes of the root to delete. I deleted them from bottom to top to prevent changing the absolute position of the nodes as I deleted them. Note that the 13th node is not determined by the data or the attributes of the node. Just by the ordinal position from the root.

I build the XML value in a variable by selecting the XML data from the table, updating it before I run the update, and then updating the row with the variable.
 

BEGIN TRAN

DECLARE

  @UserId INT = (SELECT UserId FROM User WITH (NOLOCK)  WHERE FIRSTNAME = 'Person' AND LASTNAME = 'Someone')

, @ObjectID varchar(99) = (SELECT ObjectID From Object WITH (NOLOCK) WHERE Name = 'Object name')

, @ResponseId INT

SET @ResponseId = (SELECT MAX(ObjectResponseID) From ObjectResponse WITH (NOLOCK) WHERE UserId = @UserId AND ObjectID = @ObjectID)

--Verify correct response is targeted

IF @UserId IS NOT NULL

BEGIN

IF @ResponseId = 123456

BEGIN

SELECT ObjectResponseXML Before FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

DECLARE @XDoc XML

SET @XDoc = (SELECT ObjectResponseXML FROM ObjectResponse WHERE ObjectResponseID = @ResponseId)

--Remove 13 before 12 to retain absloute order of preceding nodes you will delete

SET @XDoc.modify('delete (/ResponseRoot/Answer[13])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[12])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[11])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[10])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[9])')

UPDATE ObjectResponse

SET ObjectResponseXML = @XDoc

WHERE ObjectResponseID = @ResponseId

SELECT ObjectResponseXML After FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

END

ELSE Print 'Object response not found.'

END

ELSE Print 'User not found.'

ROLLBACK