Converting SQL SERVER DateTime? Test it thoroughly….

Today one of my user complained that a report was missing one particular ticket from the output. The report in question is very simple, it accepts start date & end date and filters on the ticket creation date time column to get the list of tickets.

The report query was something like

Select * from table1 where convert(datetime,CONVERT (int, CreatedDate)) between ’2012-01-01′ AND ’2012-01-31′

Cursory look at the query looked fine. However I was proved wrong when I ran the query in SSMS. It was missing a ticket whose CreatedDate  was ’2012-01-31 18:57′

After some troubleshooting , I figured out that the issue was due to the conversion of CreatedDate to INT. In the query, the date was first converted to INT to remove the time part and then again converted to date time for comparison.

When the time part is in PM, the conversion to INT, converts it to next day. The below screenshot will explain it more precisely

Handling DateTime in SQL SERVER is very tricky especially when you are some processing like conversion, finding datediff etc. The only option we have is to test it out thoroughly.

About these ads

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in T-SQL. Bookmark the permalink.

10 Responses to Converting SQL SERVER DateTime? Test it thoroughly….

  1. Wise Old Man says:

    I’m not sure why you would convert it to an INT to get rid of the time. In older versions of sql, I’ve always used this: CONVERT(datetime, CONVERT(varchar, TheDate, 101)). I’m not sure what the Best Practice is for SQL 2008 and above.

  2. Rich M. says:

    Sorry, but converting DATETIME to INT is the wrong way to do this. The more efficient way to code this is to skip converting from DATETIME to INT by using built-in SQL functions. Using your approach, the optimizer cannot use any index created on the DATETIME column, b/c you’ve replaced the indexed column CreatedDate with a function. Try running this on a few million rows and you’ll see the difference.

    The better way is to look for dates that are greater than or equal to @DateFrom and less than the start of the day after @DateTo. The example code below has a WHERE clause that is comparing [d] — an actual column, not a function — to some value, which means an index on [d] can be used to run the query.

    DECLARE @DateFrom DATETIME, @DateTo DATETIME;
    SET @DateFrom = ’2012-02-11′;
    SET @DateTo = ’2012-02-25′;

    CREATE TABLE #d (i INT, d DATETIME);
    INSERT INTO #d
    (i,d)
    SELECT 1, ’2012-02-01 12:33′ – Before @DateFrom
    UNION
    SELECT 2, ’2012-02-11 08:55′ – BETWEEN @DateFrom and @DateTo
    UNION
    SELECT 3, ’2012-02-25 09:23′ – This one is after @DateTo

    SELECT *
    FROM #d
    WHERE d >= @DateFrom AND
    d < DATEADD(dd, DATEDIFF(dd, 0, @DateTo) + 1, 0) — Beginning of next day

    drop table #d

  3. Richard says:

    Agreed – converting to int is the wrong way to drop the time.

    For SQL 2008, try:
    Convert(datetime, Convert(date, @Value))
    For earlier versions, try:
    Convert(datetime, DateDiff(dd, 0, @Value), 1)

    And, as Rich said, don’t apply this to a column; use a range comparison instead.

  4. Wise Old Man/Rich M/Richard,

    Thanks for your comment.

    I agree 100% with you guys that this was bad approach. This report was written long back by someone else and I happened to troubleshoot it. Having said that I have seen many people using this. In-fact I saw in one of the discussion in a forum like in the below query both columns are same

    select datediff(day, ’19000101′, getdate()), cast(getdate() as int);

    If someone runs this in the morning, they will think yes both are same, but it is not the case.

  5. broby425 says:

    I’ve found this to be a handy way of manipulating dates:

    – Declare the variable
    DECLARE @DATE DATETIME

    – Set the variable
    SET @DATE = GETDATE()

    SELECT @DATE
    – Variable looks like this:
    – 2012-03-08 07:47:57.600

    SELECT CONVERT(CHAR(8), @DATE, 112)
    – This drops the time and preserves only the date:
    – 20120308

    – Converting to CHAR value, then casting as datetime…
    SET @DATE = CAST(CONVERT(CHAR(8), @DATE, 112) AS DATETIME)

    SELECT @DATE
    – …gives you the date only, without the time:
    – 2012-03-08 00:00:00.000

  6. Eric Russell says:

    This following example, where I’m determining if a date falls within a lower/upper date range, has always worked reliably. Also, because search expression includes a reference to the actual CreatedDate column instead of a function, it can be indexed.
    Select * from table1 where CreatedDate >= ’2012-01-01′ AND CreatedDate < ’2012-01-31′;

  7. dvroman says:

    broby425 got it right. Just need to add:
    CONVERT(VARCHAR, SomeDate, 111)
    will create a date only field that also sorts correctly by character.

  8. It’s the first time I am hearing about converting a DATETIME value to INT to remove the time portion. Normally, CONVERT to VARCHAR is the best way to do this (in SQL 2005 and below) and CONVERT to DATE for SQL 2008 and up.

  9. Depending upon the usage, the calling code may also be using the BETWEEN comparison to search for values while keeping the time portion as-is.

  10. Jim Etheridge says:

    This appears to work for all versions of SQL:

    CAST(datetimevar as CHAR(11))

    It will lop off the time portion of a datetime value, and, even though it converts the value to CHAR, can still be used for comparison against a datetime, or as the input to a datetime column or variable.

    In SQL 2008 the much easier way is:

    CAST(datetimevar as DATE)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s