Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

conversion of a varchar data type to a datetime

Posted on 2013-11-20
9
Medium Priority
?
370 Views
Last Modified: 2013-12-10
hi guys

I am using sql server 2012

This is my sql which works fine in 2008
SELECT distinct NAME,DATE FROM WAYNE_CUSTOMERS
WHERE UP_DT BETWEEN '2013-10-30 18:04:37.880' and '2013-10-30 18:04:37.897'

But in 2012 version i get this error

>>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Any idea how i can fix this?
thanks.
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 700 total points
ID: 39664579
Please use explicit conversion:
SELECT distinct NAME,DATE FROM WAYNE_CUSTOMERS
WHERE UP_DT BETWEEN CONVERT(datetime, '2013-10-30 18:04:37.880', 121) and CONVERT(datetime,'2013-10-30 18:04:37.897', 121)

Open in new window

0
 

Author Comment

by:royjayd
ID: 39665535
Thanks
Can you please tell me what is 121 in your SQL?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 39665677
121 is a "style" for dates recognized by the convert() function

see: SQL Server Date Styles (formats) using CONVERT()

and: http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39666594
Do not specify a conversion, or otherwise convert to datetime yourself.  You're just risking potentially serious performance issues later.

Instead, use a 100% safe literal date format, i.e., YYYYMMDD, only:

WHERE UP_DT BETWEEN '20131030 18:04:37.880' and '20131030 18:04:37.897'
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39668269
The explicit conversion of two literals leads to performance risk?  
Relying on implicit conversion produced a risk and led to this question.

I'd certainly agree that doing funky conversions on the data is a performance issue, but that's not the case here.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39669184
>> The explicit conversion of two literals leads to performance risk? <<

Not directly, but indirectly is definitely possible.  See below.


>> Relying on implicit conversion produced a risk and led to this question. <<

No, using a non-universal literal date format lead to this problem.

Why not use a format that always works, and that you don't have to explicitly convert?  Just having to lookup and use all the conversion codes is a pain anyway, leading to qs like:
"Can you please tell me what is 121 in your SQL?"

Why unnecessarily cause all that hassle?!


>> I'd certainly agree that doing funky conversions on the data is a performance issue, but that's not the case here. <<

Ah, but a forced/implicit conversion is the potential performance problem:

*) Doing the explicit conversion of the literal forces a specific data type on the result, in this case datetime:
"CONVERT(datetime, '2013-10-30 18:04:37.880', 121)"

*) If that data type has a higher precedence than the column data type it's being compared to, that might force an implicit conversion on the table column, and an implicit column conversion would prevent an index seek from being done on that column, in turn potentially causing a HUGE performance hit.  You never want SQL having to do an implicit conversion on a table column if you can avoid it.


Worse, you can even MISS rows that (arguably) SHOULD be SELECTed, because SQL didn't do an implicit conversion, but used the datetime value as you specified it ... GRRRRR.

This code demonstrates that (at least on SQL 2008, SP2):

IF OBJECT_ID('tempdb.dbo.#dates') IS NOT NULL
    DROP TABLE #dates
CREATE TABLE #dates ( up_dt date, string varchar(30) )
CREATE CLUSTERED INDEX dates__CL ON #dates ( up_dt )

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100000s].digit * 100000 + [10000s].digit * 10000 +
        [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]    
    CROSS JOIN cteDigits [10000s]
    CROSS JOIN cteDigits [100000s]
)
INSERT INTO #dates
SELECT DATEADD(DAY, tally / 20 , 0) AS up_dt, CAST(tally AS varchar(30)) AS string
FROM cteTally
WHERE
    tally > 500000
ORDER BY 1


GO

-- assume "up_dt" was originally datetime but was changed to date;
-- would you want rows being SELECTed or not!?!


-- no rows are SELECTed, even though there are 20 rows with a date of 20131030;
-- (btw, interesting that SQL doesn't force up_dt to be implicitly converted to datetime).
SELECT *
FROM #dates
WHERE up_dt = CONVERT(datetime, '2013-10-30 18:04:37.880', 121)

-- all rows are SELECTed that have a date of 20131030
SELECT *
FROM #dates
WHERE up_dt = '20131030 18:04:37.880'
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39670718
Thanks Scott, most interesting.
0
 

Author Comment

by:royjayd
ID: 39671854
thanks guys

Scott,
you said
>Instead, use a 100% safe literal date format, i.e., YYYYMMDD, only
WHERE UP_DT BETWEEN '20131030 18:04:37.880' and '20131030 18:04:37.897'

just to be clear from my observation my sql (in my original question ) worked without errors in 2008 version, the problem only arises when i run it in 2012 version.

So are you saying that something like this should work fine in 2008 and 2012 versions ?
<< SELECT distinct NAME,DATE FROM WAYNE_CUSTOMERS
WHERE UP_DT BETWEEN '20131030 18:04:37.880' and '20131030 18:04:37.897'  >>

basically by removing the 'dash' from the dates ?

thanks.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 700 total points
ID: 39674838
Yes, the 'YYYYMMDD' date format is absolutely 100% safe in every SQL version with any/all date and/or language settings.  [The only way to make the other format safe is to use "T" as the date/time delimiter: "yyyy-mm-ddThh:mi:ss.mmm".]

Here's one example:

DECLARE @up_dt datetime

SET DATEFORMAT mdy --or
--SET LANGUAGE ENGLISH

PRINT 'SET#1'
SET @up_dt = '20131030 18:04:37.880' --OK
PRINT 'SET#2'
SET @up_dt = '2013-10-30 18:04:37.880' --OK

SET DATEFORMAT dmy --or
--SET LANGUAGE GERMAN
PRINT 'SET#3'
SET @up_dt = '20131030 18:04:37.880' --OK
PRINT 'SET#4'
SET @up_dt = '2013-10-30 18:04:37.880' --OOPS!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question