CODEWITHSUNDEEP
sqlsql-server
Kat Isenberg
Kat Isenberg

Reputation: 99

Conversion failed when converting date and/or time from character string and pull Quarter/Year from Date

I have a date column that is a bigint, and does not accept nulls. The date format of the values within the column is 20190101. I need a query that tells me the year and quarter. After googling, I found a query that worked. Today it does not work. I receive this error 'Conversion failed when converting date and/or time from character string' on a query. I tracked the cause to the table allowing -1 on some rows. How can I allow for this in my query? I thought I had to convert it to a character to pull out the quarters. I did this using the cast. I changed to a datetime and it said specified scale invalid.

Here is my query:

Select Distinct left(DateKey,4) as Year,
DatePart(QUARTER,cast(cast(DateKey as char(8)) as date)) As Quarter 
From WorkersUnitePerformanceFact
Order By Year, Quarter

Both items in the select receive the error.

Upvotes: 0

Views: 155

Answers (2)

Caius Jard
Caius Jard

Reputation: 74700

You're saying that someone has inserted -1 into your column and it's tripping you up. While it's compounding the problem that this data should have been in some kind fo date column originally you can consider filtering out the undesirable values:

Select Distinct left(DateKey,4) as Year,
DatePart(QUARTER,cast(cast(DateKey as char(8)) as date)) As Quarter 
From Performance.WorkerPerformanceFact

WHERE DateKey BETWEEN 19700101 and 21991231 

Order By Year, Quarter

Adjust the WHERE clause to suit your needs of date range. I picked Jan 1 1970 and 31 dec 2199

But seriously consider changing the data type of the column now (the longer you leave it, the harder it gets..); I promise this won't be the last time this bites you in the ***; next time it will be another problem, like someone inserting 20200230 (there is no 30th of February) and you shouldn't keep trying to filter out the invalid values

The process of conversion to date doesn't have to be an instant one; you can add another DATE type column to the table, start filling it with a trigger from the DATEKEY column, switch apps to using the date column, and then drop the DATEKEY column later (to retain it for a while, populated from the true date column) all over the course of several revisions of the app

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Convert the value to a string, then a date:

convert(date, convert(varchar(255), 20190101))

Then you can construct a year/quart using datename():

select (datename(year, convert(date, convert(varchar(255), 20190101))) + 'Q' +
        datename(quarter, convert(date, convert(varchar(255), 20190101)))
       )

Upvotes: 1

Related Questions