Search results matching tags 'T-SQL Tuesday' and 'datetime'

Unambiguous date formats : T-SQL Tuesday #001

jamiet — Tue, 08 Dec 2009 20:23:41 GMT

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this:

DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing:

SELECT @@LANGUAGE

To demonstrate how your language setting can influence the results of a cast take a look at the following code:

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005

USE tempdb

GO

DECLARE @t TABLE (

      dateString  NVARCHAR(19)

);

INSERT @t (dateString)

VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'

,      ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'

,      ('20091208 18:00:00')   --'yyyyMMdd hh24:mi:ss'



SET        LANGUAGE french;

SELECT 'french' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

SET        LANGUAGE us_english;

SELECT 'us_english' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then seeing how the @@LANGUAGE setting can affect the results. Here are those results:

Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12^th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8^th December 2009.

Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you):

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008

GO

USE tempdb

GO

SET        LANGUAGE french;

DECLARE    @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format

SELECT CAST(@dt AS datetime) AS [ExplicitCast]

,      DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]

,      DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast];

Here we are doing three different things with our nchar literal:

explicitly cast it as a [datetime]
extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast)
extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime]

Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results:

(Were you correct?)

Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that?

To get the answer we can turn to BOL’s description of the DATENAME function syntax:

The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better.

These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

regardless of which version you are on!

Don’t forget to check out other T-SQL Tuesday blog posts, see Adam Machanic’s blog post Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks for details!

@Jamiet

T-SQL Tuesday #001: Exploring "Fuzzy" Interval Islands Using SQLCLR

Adam Machanic — Tue, 08 Dec 2009 19:13:00 GMT

When working with time intervals, we often want to ask a couple of basic questions:

Which time periods are not covered by our intervals? These are known as "gaps".
What are the time ranges that we are fully covering? These are known as "islands".

If you're unfamiliar with "gaps" and "islands" I highly recommend reading some of Itzik Ben-Gan's recent work in SQL Server Magazine. He's had a great series going on the topic. But one thing that he hasn't found a good T-SQL solution for is a problem that I call "fuzzy islands."

When is an island fuzzy? When it doesn't necessarily have a fixed end time. For an example of this, consider a store, selling a number of products. Management might want to see a report showing when each product was selling. This is, effectively, an island question. The goal is to find all of the covered ranges during which sales occurred. But running such a report, you might find that way too much data is returned. A given product may have sold units on Monday, Tuesday, and Thursday, but for some reason no one bought one on Wednesday. Creating a new island every time there is a small gap will create a 300-page report where a 1-page dashboard might suffice--not a good user experience, nor a good way of representing the data. The solution? Introduce a bit of fuzziness--a rule that says, for instance, that a gap is only a gap if it's longer than 7 days.

Answering the fuzzy islands question is not a very difficult thing to do in T-SQL. The basic algorithm follows:

Find all of the "start" dates or times. These are simply those dates or times for which a previous date or time in the fuzzy interval does not exist. So if a row is dated 2009-12-08 and our fuzzy granularity is 7 days, we know we have a start date if there is no other covered data from the end of November.
For each start date identified, find the minimum date greater than the start date. This is done by looking ahead rather than behind, so if our date is 2009-12-08 and we have a granularity of 7 days, we'll look forward until December 15th.
Optionally, add the fuzzy factor to the end date. This is something that I think is a good idea, as it introduces the concept of an "active" interval--a period over which, for example, a product is considered to have been selling. The interval shouldn't necessarily terminate the day that the last sale occurred. But of course this depends on the situation in question.

The following query produces a fuzzy islands report for each product in the AdventureWorks (or AdventureWorks2008) Production.TransactionHistory table. You can modify the @active_interval variable to tweak the fuzziness and change the output.

--Find all "active" product time ranges, meaning that the product
--has sold within the previous 7 days
DECLARE @active_interval INT = 7

SELECT DISTINCT
    t_s.ProductID,
    t_s.TransactionDate AS StartDate,
    DATEADD
    (
        dd,
        @active_interval,
        (
            SELECT
                MIN(t_e.TransactionDate)
            FROM Production.TransactionHistory AS t_e
            WHERE
                t_e.ProductID = t_s.ProductID
                AND t_e.TransactionDate >= t_s.TransactionDate
                AND NOT EXISTS
                (
                    SELECT *
                    FROM Production.TransactionHistory AS t_ae
                    WHERE
                        t_ae.ProductID = t_s.ProductID
                        AND t_ae.TransactionDate BETWEEN 
                            DATEADD(dd, 1, t_e.TransactionDate) 
                            AND DATEADD(dd, @active_interval, t_e.TransactionDate)
                )
        )
    ) AS EndDate
FROM 
(
    SELECT DISTINCT
        ProductID,
        TransactionDate
    FROM Production.TransactionHistory
) AS t_s
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM Production.TransactionHistory AS t_ps
        WHERE
            t_ps.ProductID = t_s.ProductID
            AND t_ps.TransactionDate BETWEEN 
                DATEADD(dd, -@active_interval, t_s.TransactionDate) 
                AND DATEADD(dd, -1, t_s.TransactionDate)
    )
ORDER BY
    ProductID,
    StartDate
GO

Running this query you'll find that it works... But the results are returned a bit more slowly than we might desire--15 to 16 seconds on my end. Looking at the query plan, the reason for this becomes quite obvious: Lots and lots of table scans. How can we eliminate all of the overhead?

SQLCLR to the rescue. The best way to solve this problem--at least until the SQL Server team adds proper OVER clause support (LAG and LEAD, specifically)--is to use a cursor algorithm. We could do this in a T-SQL cursor, but why bother? Cursor logic in SQLCLR is much, much faster.

To solve the problem, I implemented an enumerator, called active_products_enumerator. The enumerator is initialized using a SqlDataReader and an "active interval" -- the number of days we're allowing for fuzziness. The DataReader is expected to return rows ordered by ProductID and TransactionDate. The enumeration process uses the following algorithm:

If the current ProductID is not the same as the previous ProductID, return an end date for the previous interval and start a new one
If the current period date is greater than the previous period date plus the active interval, return an end date for the previous interval and start a new one
Otherwise, continue

Following is the MoveNext method for the enumerator (the complete code is attached to this post so that you can run it on your end without my bombarding you with a gigantic code-filled post):

public bool MoveNext()
{
    try
    {
        current_results = null;

        while (r.Read())
        {
            new_ProductID = r.GetSqlInt32(0);
            new_period_date = r.GetDateTime(1);

            if (new_ProductID != ProductID)
            {
                if (ProductID != 0)
                {
                    current_results = new results(
                        ProductID,
                        StartDate,
                        period_plus_interval);
                }

                ProductID = new_ProductID;
                StartDate = new_period_date;
                period_plus_interval = new_period_date.AddDays(activeInterval);

                if (current_results != null)
                    return (true);
            }
            else
            {
                if (period_plus_interval < new_period_date)
                {
                    current_results = new results(
                        ProductID,
                        StartDate,
                        period_plus_interval);

                    StartDate = new_period_date;
                }

                period_plus_interval = new_period_date.AddDays(activeInterval);

                if (current_results != null)
                    return (true);
            }
        }

        //return the last row of data
        if (ProductID != 0)
        {
            current_results = new results(
                ProductID,
                StartDate,
                period_plus_interval);

            //set this to 0 so we don't return another row
            ProductID = 0;
        }

        if (current_results != null)
            return (true);
        else
        {
            r.Dispose();
            return (false);
        }
    }
    catch
    {
        r.Dispose();
        throw;
    }
}

Put into a table-valued function, as the attached code does, this algorithm will return the same data as the T-SQL query in under a third of a second on my end--around 45 times faster than the T-SQL version.

Note that I've played some games with a loopback connection to get this whole thing to work. That's a topic for a future blog post, so stay tuned. In the meantime, please realize that you'll have to catalog the assembly with EXTERNAL_ACCESS permission to make this happen.

This post was created for T-SQL Tuesday, the revolving SQL Server blog party, hosted this month by... me. Enjoy!

T-SQL Tuesday #001: My Datetime FAQ

Kalen Delaney — Tue, 08 Dec 2009 18:49:00 GMT

I am rising to Adam's challenge for his very first T-SQL Tuesday, and blogging about datetime issues. Since I just finished a 9-hour teaching day in Stockholm, and I'm incredibly jetlagged, I decided to keep this simple. I'm very much looking forward to what everyone else is going to say!

For as long as I've been providing TSQL support, starting over 22 years ago while working for Sybase, there have been confusions about datetime data storage and retrieval. I have seen the same questions over and over for all of those years, so I am listing the all-time most common datetime questions here. My FAQ stands for "Frequently ANSWERED Questions"! To keep the post simple, I am not going into complete detail for every answer. If you want more details you can read one of my books. I am also just listing the tip of the iceberg in terms of common datetime questions.

1. I have my dates stored like this: "March 4, 1948" but SQL Server is returning them like this: "1948-03-04 13:17:32.120". How can I get the dates returned in the manner I stored them?

This question actually can't be answered as written, because it is NOT true that a date could be stored as "March 4, 1948". A character string could be stored that way, but not a date. Internally, datetime data is stored in a format you never see displayed, no matter how you enter the datetime value. It is stored as two integers, the first being the number of days before or after the base date of "January 1 1900" and the second integer being the number of milliseconds after midnight.

You can actually see these two parts if you convert a datetime value to a binary string of 8 hexadecimal bytes. The first 4 hexadecimal bytes are the number of days before or after the base date, and the second 4 bytes are the number of clock ticks after midnight. You can then convert these 4-byte hexadecimal strings to integers.

You can see the component parts of the current date and time by using the system function getdate(). I first store the current date and time in a local variable so I can be sure I'm using the same value for both computations.

DECLARE @today datetime
SELECT @today = getdate()

SELECT @today
SELECT CONVERT (varbinary(8), @today)
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 1, 4))
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 5, 4))

The way a datetime value is displayed depends on several factors, including the tool you are using and your machine's regional settings. If you want to control the way a datetime value is displayed, you can convert it to a character string.

2. Why doesn't this query find any rows: SELECT * FROM orders WHERE orderdate = 'March 4, 2009'

The most like reason is because datetime data always includes a time portion as well as a date portion. Unless your orders were entered at a time of midnight (the default), they won't match the datetime value shown. (This answer frequently leads to the next question.)

3. Why can't I store just a date or just a time, why do I always need both?

The simple answer, prior to SQL Server 2008 is "just because". That is the way SQL Server works. Microsoft has been promising individual date and time datatypes for several versions now, and they finally showed up in SQL Server 2008, as well as a couple of other new datetime datatypes, plus the ability to specify a precision for fractional part a second.

4. Why doesn't this query show me how old I am? SELECT datediff(yy, @my_birthdate, getdate() )

Arithmetic with datetime values works only with whatever datepart is specified as the first parameter and does no rounding. Since my first parameter is yy, for year, the datediff simply subtracts the year component of my birth year from the year component of today's date. If I haven't had my birthday yet, it will show me how old I will be on my birthday this year, which will not be my current age. And since birthday is almost at the end of the year, for most of the year I will get an incorrect number.

5. Why can't I store dates earlier than 1753 using the datetime datatype?

This was done for historical reasons, and started with the original Sybase specification for the datetime datatype. In what we sometimes refer to as the western world, we have had two calendars in modern time: the Julian and the Gregorian calendars. These calendars were a number of days apart (depending on which century you look at), so when a culture that used the Julian calendar moved to the Gregorian calendar, they dropped between 10 to 13 days from the calendar. Great Britain made this shift in 1752, and in that year, September 2^nd was followed by September 14^th. Sybase decided not to stored dates earlier than 1753 because the date arithmetic functions would be ambiguous. However, other countries made the change at other times, and in Turkey the calendar was not shifted until 1927.

The new date and datetime2 times in SQL Server 2008 do allow us to store dates back to 0001-01-01.

For one of the best articles on the web about datetime issues in SQL Server, see Tibor Karaszi's website:

http://www.karaszi.com/SQLServer/info_datetime.asp

I love the calendars in Europe, especially Germany and Sweden, because the first part of the word is my name!

~Kalen

T-SQL Tuesday #001: Yesterday it worked, today it's not working...

Alexander Kuznetsov — Tue, 08 Dec 2009 16:06:00 GMT

But did it actually work yesterday?

If the day is Friday, Noverber 13th, or the 13th day of any other month, and your query blows up for the first time, one place to search for is character strings converted to datetime values. The following script illustrates the problem:

-- yesterday it worked, or did it? SET LANGUAGE US_English; SELECT CAST('11/12/2009' AS DATETIME);

SET LANGUAGE Norwegian; SELECT CAST('11/12/2009' AS DATETIME);

Changed language setting to us_english.

-----------------------
2009-11-12 00:00:00.000

(1 row(s) affected)

Changed language setting to Norsk.

-----------------------
2009-12-11 00:00:00.000

(1 row(s) affected)

-- today it's not working SET LANGUAGE US_English; SELECT CAST('11/13/2009' AS DATETIME);

SET LANGUAGE Norwegian; SELECT CAST('11/13/2009' AS DATETIME);

Changed language setting to us_english.

-----------------------
2009-11-13 00:00:00.000

(1 row(s) affected)

Changed language setting to Norsk.

-----------------------
Msg 242, Level 16, State 3, Line 11
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Note that the query did not quite work yesterday either - although it did not raise errors, it was selecting the wrong data.

Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks

Adam Machanic — Mon, 30 Nov 2009 17:47:00 GMT

T-SQL Tuesday is the SQL Server blogosphere's first recurring, revolving blog party. The idea is simple: Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called "T-SQL Tuesday", but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you're working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it.

The posts must go live on the second Tuesday of the month, by (or before) 23:59 PST. Each post must link back to the host blog's post, and the blogger should make sure that a trackback has been generated and successfully posted to the host blog; if not, a comment should be posted on the host blog with a link, so that the host blogger can find the post. The host blogger will then take all of the posts and within a day or two will compile a "roundup"--a brief summary with links--so that readers can find all of the relevant posts.

Why do this? Several reasons: It's fun, it's going to generate a lot of interesting content, and it's going to bring lots of bloggers into the fold and improve our overall community by creating a lot of links between blogs. Any blogger is invited to participate, whether the blog has been live for six days or six years. If you can write, you should join in the fun.

Rules of engagement for hosts: Although anyone can join in the party, hosting has a slightly higher bar: Your blog must have been active in the last six months ("active" is defined as at least one post per month), and you must have participated in T-SQL Tuesday events on at least two prior occasions. Since that's impossible until we've had two events, I'll host the first two myself. After that it will move around to as many blogs as we can get involved--and I hope that a number of you out there decide to go for it. The host blogger is not required to write a participating post to be included in the roundup, but is free to do so if he or she chooses. Want to host? Contact me through my blog--I'll keep a waiting list and control it centrally so that it doesn't devolve into anarchy too quickly.

So that's that. If you bothered to read the title of this post you're aware that the topic for this month is Date/Time Tricks. Write a blog post that talks about dates and times--this can be based around T-SQL programming, data modeling, ETL, reporting, or whatever else you're using dates and times for (and who isn't?). Make sure that your post goes live on Tuesday, December 8, between 00:00 and 23:59 PST. Make it clear in your post that it's for T-SQL Tuesday, and make sure it links back here. Finally, make absolutely certain that a comment shows up here so that I can find the post.

Questions? Comments? Post here.

Enjoy! I'm looking forward to the start of a great blog tradition in the SQL Server community.