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

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?

CodePudding user response：

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.

CodePudding user response：

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));

CodePudding user response：

If p.finalGame is already a DATE or DATETIME type, you need to extract the year portion for comparison.

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

If p.finalGame is a character type, you need to either quote your year value to do a string compare (not recommended) or convert your string date to a proper date before extracting the year for comparison

WHERE (p.finalGame is null or YEAR(CONVERT(DATE,p.finalGame)) >= 2018);