Thank you.

I am ready to write an article about "DATETIME tricks". What now? Steve has to accept?

Yes, exactly as that. Except "mm" should be either "mi", "n" or "minute".

Using "mm" gives you 0 as result because "mm" denotes difference in months [boundary crossings].

"mi", "n" or...

You mean some kind of running total?

Seems like a lot of math calculations there...

See this http://weblogs.sqlteam.com/peterl/archive/2010/09/08/fast-easter-day-function.aspx

DECLARE@Data XML ='<?xml version="1.0" encoding="UTF-8"?>

<xml>

<target>

<tag_pairs>

<tag_pair>

<name>addr1</name>

<values>

<value>20 Morris Ave</value>

</values>

</tag_pair>

<tag_pair><name>addr2</name><values><value></value></values></tag_pair>

<tag_pair><name>addr3</name><values><value></value></values></tag_pair>

<tag_pair><name>city</name><values><value>Wahroonga</value></values></tag_pair>

<tag_pair><name>state</name><values><value>New South Wales</value></values></tag_pair>

<tag_pair><name>zip</name><values><value>2076</value></values></tag_pair>

</tag_pairs>

</target>

</xml>'

SELECTn.value('../name[1]', 'VARCHAR(MAX)') AS Name,

n.value('value[1]', 'VARCHAR(MAX)') AS Value

FROM@Data.nodes('/xml/target/tag_pairs/tag_pair/values') AS v(n)

No, the table is NOT declared for each loop. You can confirm that at the final result.

However, I consider this bad practice and confusing to put the table declaration inside...

Some more datatime tricks...

DECLARE @FromDate DATETIME,

@ToDate DATETIME

SELECT@FromDate = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), '02:00'),

@ToDate = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), '03:00')

SELECT Database_Name,

Backup_Finish_Date,

...

WayneS is spot on. The table is declared not matter what AT the location in code where it is so all following code is referencing it correctly.

And just to be perfectly safe, use QUOTENAME

SET @sql = 'CREATE TABLE ' + QUOTENAME(@otherdb) + '.[dbo].[my_new_table]'

If you are using the parameter to insert into a datetime column, there is ABSOLUTELY no need to convert the date value.

Keep the date value in ISO YYYYMMDD date format...

I have a solution which yields this statistics

Table 'Worktable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob...

As I repeatedly have told you here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152250

We don't know what your tables are named, what information is stored where and so on.

Also, we have no idea of foreign keys,...

I think Jeff means extending date ranges.

If you have three date ranges

20100101-20100630, 20100515-20100930 and 20100928-20101010,

the end result is 20100101-20101010.

Neither.

SQL Server is smart enough to move all...

There will be only one copy of the table variable.