CODEWITHSUNDEEP
sql-servert-sqlpowerbi
Mike Milly
Mike Milly

Reputation: 1

Error converting string, '2015-08-23', to date

I am trying to clean my data prior to loading into PowerBI to create the visuals. I have created my query as:

CREATE VIEW Project2 AS (
 Select 
  p.playerID as ID1, 
  p.birthYear, 
  p.birthMonth, 
  p.birthDay, 
  p.birthCity, 
  p.deathYear, 
  p.nameFirst, 
  p.nameLast, 
  p.nameGiven, 
  p.weight, 
  p.bats, 
  p.throws, 
  p.finalGame, 
  b.*
  from dbo.People as p
      LEFT JOIN dbo.Batting as b
   ON p.playerID=b.playerID
   and b.G >= 50
   WHERE (p.finalGame is null or p.finalGame >= 2018));

This works great until attempting to load the view into PowerBI I get this error:

DataSource.Error: Microsoft SQL: Conversion failed when converting the varchar value '2015-08-23' to data type int. Details:
DataSourceKind=SQL
DataSourcePath=laptop-o4rhi9q7;Baseball
Message=Conversion failed when converting the varchar value '2015-08-23' to data type int.
ErrorCode=-2146232060
Number=245
Class=16

I can't figure out where /how to utilize cast(p.finalGame as date) in correct syntax, any ideas?

Upvotes: 0

Views: 734

Answers (3)

T N
T N

Reputation: 9907

Based on the error message, you date appears to be a string of the form 'yyyy-mm-dd', not a true date type or an integer year. I believe your fix is to quote your year. Either

WHERE (p.finalGame is null or p.finalGame >= '2018');

or

WHERE (p.finalGame is null or p.finalGame >= '2018-01-01');

If your year is a variable, convert it to a string with something like CONVERT(VARCHAR(10), @Year). (This assumes a 4 digit value.)

As already noted, a better approach (if you have the ability to change the schema) is to redefine finalGame as a true DATE or DATETIME type. Then you could compare p.finalGame >= '2018-01-01' or p.finalGame >= DATEFROMPARTS(@Year, 1, 1).

Upvotes: 0

Charlieface
Charlieface

Reputation: 71144

You must use a proper date literal for this, which means it must be enclosed in quotes, and ideally use a non-ambiguous format

WHERE (p.finalGame is null or p.finalGame >= '20180101'));

If finalGame is actuall varchar you would need to convert it using an appropriate conversion type. For example

WHERE (p.finalGame is null or CONVERT(date, p.finalGame, 102) >= '20180101'));

That may not be the correct format number, the full list is here.

I urge you to change the column type to date in the first place.

Upvotes: 1

Jenga
Jenga

Reputation: 63

If the data for p.finalGame = '2015-08-23', you'll need to cast that as a date in your WHERE clause.

   CREATE VIEW Project2 AS (
    Select 
     p.playerID as ID1, 
     p.birthYear, 
     p.birthMonth, 
     p.birthDay, 
     p.birthCity, 
     p.deathYear, 
     p.nameFirst, 
     p.nameLast, 
     p.nameGiven, 
     p.weight, 
     p.bats, 
     p.throws, 
     p.finalGame, 
     b.*
     from dbo.People as p
         LEFT JOIN dbo.Batting as b
      ON p.playerID=b.playerID
      and b.G >= 50
      WHERE (p.finalGame is null or cast(p.finalGame as DATE) >= 2018));

Upvotes: 0

Related Questions