Someone ask me about working with dates recently in TSQL...I was too busy at the time to stop and remember this...I did get back to them later ;-)

Here it is. I'm sure most people know this, but at least I want forget next time...

DECLARE @CurrentDateWithTime datetime

DECLARE @CurrentDateZeroTime datetime

DECLARE @TomorrowZeroTime datetime

SET @CurrentDateWithTime = GETDATE()

SET @CurrentDateZeroTime = CAST(FLOOR(CAST(@CurrentDateWithTime AS float)) AS datetime)

SET @TomorrowZeroTime = CAST(CEILING(CAST(@CurrentDateWithTime AS float)) AS datetime)

PRINT @CurrentDateWithTime

PRINT @CurrentDateZeroTime

PRINT @TomorrowZeroTime

Why does it work? Add the following PRINT statements and it becomes obvious.

PRINT CAST(@CurrentDateWithTime AS float)

PRINT CAST(@CurrentDateZeroTime AS float)

PRINT CAST(@TomorrowZeroTime AS float)

The output should be :

Mar 26 2008  6:22PM

Mar 26 2008 12:00AM

Mar 27 2008 12:00AM

39531.8

39531

39532

This was from an email I sent to some peeps for a quickie how-to...I was told that this kind of junk was worth posting on a blog.  Of course I want to do my part in cluttering up the world...

DECLARE @x as xml
SET @x = '<test att="1">
                  <node1 att="test att val node 1">test data 1</node1>
                  <node1 att="test att val node 2">test data 2</node1>
              </test>' --query a node set like a table
SELECT xd.rows.value('(.)','nvarchar(50)') AS result
FROM   @x.nodes('/test/node1') xd(rows)
--WHERE xd.rows.value('(.)','nvarchar(50)') LIKE '%test data 1'--to get values of attributes and nodes
select @x.value('(/test/@att)[1]', 'int' )--select @x.value('(/test/node1/@att)[1]', 'nvarchar(50)' )
--select @x.value('(/test/node1/@att)[2]', 'nvarchar(50)' )
select @x.value('(/test/node1)[1]', 'nvarchar(50)' )
--select @x.value('(/test/node1)[2]', 'nvarchar(50)' ) --to get nodes/sections/sets/doc fraagments
select @x.query('test/node1')

FYI, the way to dynamically create the xPath statement : use sql:variable to amend the xPath statement - if you try string concats you'll find this out ;-)