CODEWITHSUNDEEP
sqlsql-servert-sql
user2961971
user2961971

Reputation: 287

Weird error using CONVERT function

I have a database set up as 

Account (accountNo, ownerNo, balance)
Owner (ownerNo, firstName, lastName)
Event (id, accountNo, event, amount, eventDate)

So I am trying to List all transactions (Event table) for July for account 1 by doing so; 

CREATE VIEW FourA AS 
   SELECT 
      id, accountNo, event, amount, 
      CONVERT(varchar, eventDate, 103) AS eventDate
   FROM 
      Event
   WHERE 
      accountNo = 1 AND eventDate >= '01/07/2014' AND eventDate < '31/07/2014'

The first time I ran this, it ran perfectly. Now I'm getting an error for the CONVERT function:

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

HOWEVER! When I run:

select 
    CONVERT(varchar, eventDate, 103) AS eventDate
from 
    Event

it gives the perfect result of: 

eventDate
01/01/2014
07/11/2014
08/01/2014 
01/01/2014
07/10/2014
08/02/2014

So clearly it's not wrong. But why do I keep getting this weird error? Does anyone know?

NOTE: this is in SQL Server

Upvotes: 0

Views: 70

Answers (1)

cha
cha

Reputation: 10411

The problem is not within your CONVERT function. The problem is because of the implicit conversion of the date literals in your WHERE clause. I recommend you use the YYYYMMDD format for the date literals:

CREATE VIEW FourA AS 
SELECT id, accountNo, event, amount, CONVERT(varchar(10), eventDate, 103) AS eventDate
FROM Event
WHERE accountNo = 1 AND eventDate >= '20140701' AND eventDate < '20140731'

As commented below, if you do not want the time component in the result string use VARCHAR(10) when converting the date

Upvotes: 1

Related Questions