Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a proper datetime column would be preferable). I'm trying to do a query that gets some year-to-date results with BETWEEN DateTime field AND NOW().

SQL Server table:

Id  DateTime                    Ip              City            State       Country         Continent   UserAgent
-----------------------------------------------------------------------------------------
1   2013-02-05T17:58:45-06:00   68.55.34.32     Laurel          Maryland    United States   North America   NULL
2   2013-02-05T17:58:52-06:00   70.88.57.62     Port Charlotte  Florida     United States   North America   NULL
3   2013-02-05T17:59:06-06:00   71.3.202.110    Cape Coral      Florida     United States   North America   NULL
4   2013-02-05T17:59:23-06:00   67.239.18.161   Naples          Florida     United States   North America   NULL
5   2013-02-05T17:59:58-06:00   96.19.107.135   Gulfport        Mississippi United States   North America   NULL

SQL query:

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE 
   (DateTime BETWEEN CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AND { fn NOW() })
ORDER BY Ip

I'm getting an error

Conversion failed when converting date and/or time from character string

This query works...

SELECT CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AS Expr1

I've also tried CAST()...

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (DateTime BETWEEN CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AND { fn NOW() })
ORDER BY Ip

That gives the same conversion error, but this works...

SELECT CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AS Expr1

I'm sure that I'm missing some fundamental bit, but it seems like it should work.

What am I doing incorrectly?

JJ

share|improve this question
    
Do all the values have the -06:00 or does the timezone offset vary? –  ypercube Apr 7 at 20:15
1  
> not a datetime data type, but varchar(50) WHY? WHY? WHY? –  Aaron Bertrand Apr 7 at 20:51
    
@ypercube Sometimes, it is -05:00. I'm not really concerned with the specific times. I was just trying to get some year-to-date figures. That why I was trying to convert to a datetime type (so that I could use NOW()). –  doubleJ Apr 8 at 1:31
1  
@AaronBertrand Why? Because I thought that a time format that included an offset would be better than one that didn't. Plus, that data is being pushed from a website and I didn't know how to push that data as a datetime (string and int, I know). It was something that needed to be done and I did it to the best of my ability, at the time. It was a simple lack of knowledge. –  doubleJ Apr 8 at 1:33

3 Answers 3

up vote 1 down vote accepted

The issue is that you cannot CONVERT or CAST a VARCHAR ISO8601 datetime with an offset to a DATETIME.

From SQL Server 2008 onwards, the DATETIMEOFFSET datatype was introduced to handle datetimes with offsets.

As answered elsewhere, you would need to CAST your DateTime VARCHAR column to a DATETIMEOFFSET

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE 
   (
       CAST(DateTime AS DATETIMEOFFSET) BETWEEN 
         CAST('2014-01-01T00:00:00-06:00 AS DATETIMEOFFSET) 
         AND { fn NOW() }
   )
ORDER BY Ip
share|improve this answer
    
Choosing this as the answer only because it is the one that most does what I was trying to do (not that it is necessarily the best way). Also, thanks for including that DATETIMEOFFSET will only work on SQL Server 2008 and newer. ypercube's answer may be better for older versions. –  doubleJ Apr 9 at 21:13
    
Interestingly enough, the two other answers gave the same number of results. This answer was less, by 20ish. We're talking about 15000ish results, so the discrepancy isn't huge. I wonder if the difference is in dateadd() versus NOW()? –  doubleJ Apr 9 at 21:24

Why don't you just use?:

WHERE DateTime >= '2014-01-01'
  AND DateTime < CONVERT(CHAR(19), DATEADD(second, 1, GETDATE()), 126)

Advantages:
- No values in the varchar column will be converted so you'll get no errors
- Efficiency as indexes can be used

Disadvantages:
- does not take care of timezone offsets
- some of the values, while being invalid as datetimes (examples '2014-01-33T25:61:62', '2014 Sunday 13th of April'), they will still be returned by the query.

share|improve this answer
    
I'll have to test that out. I didn't think about converting the current time to iso8601. I was trying to convert iso8601 to datetime. –  doubleJ Apr 8 at 1:35

Try type casting to datetimeoffset type as below:

CAST(datetime as DATETIMEOFFSET)

For instance your query will become something like following:

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (CAST(datetime as DATETIMEOFFSET)
BETWEEN CAST('2014-01-01T00:00:00-06:00' AS datetimeoffset) 
AND dateadd(day, 1, SYSDATETIMEOFFSET()))
ORDER BY Ip
share|improve this answer
1  
Oh, I wonder if part of the problem was that I was just doing DateTime in the WHERE clause. I see that you're actually doing CAST(), twice. I was only doing it, once. –  doubleJ Apr 8 at 1:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.