Nice idea, Peter! Thanks for sharing…

Regards,
Itzik

DianaMay 4/6/2007 2:52:15 PM

The following feedback was sent to Izik by reader Peter Larsson ... Diana

I read your third article today about DATETIMEs. I recently too wrote an article about DATEDIFF function here http://www.sqlteam.com/item.asp?ItemID=26922


In the following conversation, I put together a little test script for getting last day of a period, such as MONTH, QUARTER and YEAR.
This without your extra substractions.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80880


The trick is to use the date -1, which incidentally is December 31, 1899. The trick is the 31 days in December.


SELECT CURRENT_TIMESTAMP AS Now,

Status,

DATEADD(MONTH, DATEDIFF(MONTH,
StartingPoint1, CURRENT_TIMESTAMP),
StartingPoint2) AS [MONTH],

DATEADD(QUARTER, DATEDIFF(QUARTER, StartingPoint1, CURRENT_TIMESTAMP),
StartingPoint2) AS [QUARTER],
DATEADD(YEAR, DATEDIFF(YEAR, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [YEAR]


FROM (

SELECT 'End Previous' AS Status, 0 AS StartingPoint1,
-1 AS StartingPoint2
UNION ALL

SELECT 'Start Current', 0, 0 UNION ALL

SELECT 'End Current', -1, -1 UNION ALL

SELECT 'Start Next', -1, 0
) AS x

ORDER BY 5

I like your Black-belt articles, but I thought this time your calculations could be made simpler.

Peter Larsson
peso@developerworkshop.net

DianaMay 4/6/2007 2:47:52 PM

dear Itzik,
it would be nice to learn from you how to manage datetime information in UTC format. I'm currently involved with the consolidation of a system, where datetime fields are expressed in local format (time zone) and have to be transformed consistently in UTC form.
Good matter for a new article of yours!

Thank you
marco buttazzoni

mbuttazzoni 3/30/2007 4:23:12 AM