Avatar of dbaSQL
dbaSQL
Flag for United States of America asked on

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

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)
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
dbaSQL

8/22/2022 - Mon
Patrick Matthews

Cast first to a varchar:

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

Open in new window

alecomba

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

ASKER
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' ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
alecomba

DATEFROMPARTS ( year, month, day )

Returns a date value for the specified year, month, and day.
ralmada

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.
Patrick Matthews

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ralmada

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

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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zberteoc

Try this:

UPDATE tablename
SET ColumnB = NULLIF(CAST(NULLIF(ColumnA,0) AS VARCHAR),'')
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dbaSQL

ASKER
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.
Zberteoc

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.
Patrick Matthews

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zberteoc

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

ASKER
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.
Zberteoc

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dbaSQL

ASKER
yep, understood.  thank you for the input