Explicit conversion from data type bigint to date is not allowed.

dbaSQL
dbaSQL used Ask the Experts™
on
ColumnA is BIGINT, contains values like this:  20040927, 19990729, 19990722
ColumnB is DATE, it is NULL.

The table is loaded from a csv, I just need to update columnB from columnA, after the file is loaded.  


I'm just trying a basic CAST in the update, and I am receiving the error in my title line.

UPDATE tablename
SET ColumnB = CAST(ColumnA AS DATE)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Cast first to a varchar:

UPDATE tablename
SET ColumnB = CAST(CAST(ColumnA AS VARCHAR) AS DATE)

Open in new window

Commented:
update tablename
SET ColumnB=DATEFROMPARTS
                  (
                  SUBSTRING(CONVERT(nvarchar(8),ColumnA),1,4)
                  SUBSTRING(CONVERT(nvarchar(8),ColumnA),5,2)
                  SUBSTRING(CONVERT(nvarchar(8),ColumnA),7,2)
                  );

Author

Commented:
Hi matthew.  thank you for looking!

it fails w/this:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

alecomba, what is 'DATEFROMPARTS' ?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
DATEFROMPARTS ( year, month, day )

Returns a date value for the specified year, month, and day.
use this:

UPDATE tablename
SET ColumnB = convert(datetime, CAST(ColumnA AS VARCHAR), 112)

btw,

function datefromparts is new in SQL 2012, it won't work with 2008 or below.
Top Expert 2010

Commented:
dbaSQL,

That error indicates that you have at least one value for which you have invalid data.

For example:

20120631   <----- There is never a June 31
2120630   <----- Year got garbled
2012630   <----- Needs leading zero for month
220120630   <----- Repeated digit on year

As they say, garbage in, garbage out :)

Patrick
Top Expert 2010
Commented:
To find invalid values:

SELECT ColumnA
FROM tablename
WHERE ISDATE(CAST(ColumnA AS varchar)) = 0

Open in new window


To only update valid values:

UPDATE tablename
SET ColumnB = CAST(CAST(ColumnA AS VARCHAR) AS DATE)
WHERE ISDATE(CAST(ColumnA AS varchar)) = 1

Open in new window

>>That error indicates that you have at least one value for which you have invalid data.<<

I don't think that's the case, but, you can try the following...


UPDATE tablename
SET ColumnB = case when isdate(cast(ColumnA as varchar) = 1 then convert(datetime, CAST(ColumnA AS VARCHAR), 112) end
Top Expert 2010

Commented:
ralmada,

I am testing my expressions in SQL Server 2008 R2, and as long as the bigint value follows a yyyymmdd pattern, and that pattern resolves to a valid date, the nested CAST is working as planned.

And if you try to slip in a bigint value that does not follow yyyymmdd, or does not resolve to a valid date, an error results.

:)

Patrick
>> I am testing my expressions in SQL Server 2008 R2, <<

Fair enough :) I don't have a server here to test so, that's why I wasn't sure..


what I'm thinking now is that the asker actually doesn't have bad data, but maybe zeros... In that case may be she wants to have the current date? in that case I would suggest something like

UPDATE tablename
SET ColumnB = case when isdate(cast(ColumnA as varchar) = 1 then cast(CAST(ColumnA AS VARCHAR) as date) else getdate() end
Try this:

UPDATE tablename
SET ColumnB = NULLIF(CAST(NULLIF(ColumnA,0) AS VARCHAR),'')

Author

Commented:
I dropped out of site there for a little bit... sorry about that.

Patrick, your update for only the valid values suggestion works perfectly.

Ralmada and Patrick, thank you very much for all of your input!  Very insightful.

Also, ralmada, you were correct -- it was not an issue of poor data, there were just a bunch of zeros in there, along with the integer values.  patrick's update works through them just fine.

I am going to split the points a teeny bit.  while patrick's suggestion in post ID 38004291is the solution, you both provided some great input.  i appreciate your time.
Have you tried my solution? It would have dealt with 0 and/or empty strings if the case. The only difference is that I generate NULL for those values and not GETDATE().

Normally if the value is unknown or is missing should translate in NULL and not is some arbitrary value, GETDATE() in this case.
Top Expert 2010

Commented:
dbaSQL,

Glad to help!

Zberteoc,

My expression already covers null values.  The ISDATE(CAST(ColumnA AS varchar)) returns a 0 for nulls, and since the WHERE clause specifies that that must equal 1, no attempted update occurs.  It also handles zeroes, and empty strings were never in the mix--the source column is data type bigint.

:)

Patrick
Your expression? I don't see any expression in your question.

What I mean is that my solution works fine as the other two for which you split the pints, unless you really have not null and not 0 and not '' values that are not valid dates. You ignored my solution though.

Author

Commented:
hi zeberteoc.  I didn't ignore your solution, but i understand it may have come across that way.  sorry about that.  When I got back into gear this morning, i read everything, walking through the suggestions one at a time.  patrick's was first in order, and his was the first that I tried.  it worked perfectly.  i did try yours, and yes, it worked.  i chose to split the points as I did because of all of the additional information that was provided by ramalda and patrick.  that's all.
That is fine, and keep in mind, if you don't know a value or is missing you use NULL unless otherwise is explicitly specified.

Author

Commented:
yep, understood.  thank you for the input

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial