simple insert statement issues

Question

Post reply

simple insert statement issues

dallas13

SSCertifiable

Points: 7491

October 1, 2019 at 8:07 pm

#3685119

Hello,

create table #temp2(datename datetime, lastprocessed datetime DEFAULT'getdate()' NULL)
insert into #temp2(datename) select distinct convert(date, c.ActualDate, 101) AS [DateName] from dim.calendar c 
select * from #temp2

I get below error when I run above

Msg 241, Level 16, State 1, Line 20
Conversion failed when converting date and/or time from character string.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

'getdate()' is a string and is not a valid date.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

but that's my default constraint. May be I need to define it the other way. Then its working.

ALTER TABLE #temp2 ADD CONSTRAINT DF_lastprocessed DEFAULT GETDATE() FOR lastprocessed

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Just remove the quotes from round GETDATE() in the CREATE TABLE statement. I don't recommend named constraints for temp tables (like in your second example) since it may harm concurrency: if a temp table somewhere already has a constraint called DF_lastprocessed and someone comes along in a different connection and runs the same code, you'll get an error message because the constraint already exists. That's right - temp table names are uniquified but constraint names on temp tables aren't.

John

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

You've already learned your obvious problem of confusing a system function call with a string. But the truth is it's worse than that. You don't know the table must have a key. You don't know that we have temporal datatypes in SQL so you're storing things as strings as if you were still in 1950s COBOL. You don't know that the old Sybase getdate () system function has been replaced by the ANSI/ISO standard current_timestamp. In short, your posting should have looked like this:

CREATE TABLE Foobar

(foo_date DATETIME2(0) NOT NULL PRIMARY KEY,

last_processed DATETIME DEFAULT CURRENT_TIMESTAMP );

INSERT INTO Foobar(foo_date)

SELECT C.actual_date

FROM Calendar AS C;

Of course, I have problems with this actual_date as somehow being unique in a calendar table. This makes no sense

Please post DDL and follow ANSI/ISO standards when asking for help.