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

Cast first to a varchar:

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

Select allOpen in new window

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

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' ?

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.

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

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

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

Try this:

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

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.

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.

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.

yep, understood.  thank you for the input