T-SQL query giving errors in SQL 2008

Question

Post reply

T-SQL query giving errors in SQL 2008

techzone12

SSCommitted

Points: 1798
More actions
December 16, 2010 at 12:32 pm

#244480

I have the following query. It runs fine in SQL 2000, but since the upgrade to SQL 2008 it's geving errors:
declare @lastdate datetime, @startdate datetime
set @lastdate = '12/15/2010'
select @startdate = convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " + datename(year,@lastdate) + '00:00')
==================================================
The errors:
Msg 207, Level 16, State 1, Line 3
Invalid column name ' '.
Msg 207, Level 16, State 1, Line 4
Invalid column name ' '.
==============================================
Apparently it does not know how to handle the double quotes " ". It's a syntax problem?

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm pretty sure you won't be able to run it using SS2000...

SQL Server requires single quotes, not double quotes.

The issue is within the last part of your statement: you'd need to add a space before the time part.

As a side note: What is the purpose of that statement anyway? It returns exactly the same as @lastdate.

Instead of all the datename stuff simply use select @startdate=@lastdate

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

calvo SSChampion Points: 12930 More actions · Answer 2

look into SET QUOTED_IDENTIFIER OFF

Once you do that, you'll see the next error which is datetime/char conversions.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

kevin4u06 SSCrazy Points: 2093 More actions · Answer 3

Try this

declare @lastdate datetime, @startdate datetime

set @lastdate = '12/15/2010'

select @startdate = convert(datetime, datename(month,@lastdate) + ' ' + datename(day,@lastdate) + ' ' + datename(year,@lastdate) + ' 00:00')

print @startdate

kevin4u06 SSCrazy Points: 2093 More actions · Answer 4

there should be a space for the seconds (' 00:00')

techzone12 SSCommitted Points: 1798 More actions · Answer 5

Your suggestion works, and it saves me from having to replace every " " with ' '. This code might be from the SQL 7 time. I am trying to make minimum changes to code to make work. There are hundered of lines like this.

Inserting the "SET QUOTED_IDENTIFIER OFF" sounds like the best solution.

I don't get the converstion errors. Although I was getting them before I put the "SET QUOTED_IDENTIFIER OFF" at the top. Not sure how it resolved itself!

Thanks a lot

kevin4u06 SSCrazy Points: 2093 More actions · Answer 6

kevin4u06

SSCrazy

Points: 2093

December 16, 2010 at 1:25 pm

#1263687

CAn you post your final solution.....

thank you

techzone12 SSCommitted Points: 1798 More actions · Answer 7

Use WS_Energy

go

SET QUOTED_IDENTIFIER OFF

/* B1 Substation */

PRINT "/* B1 Substation */"

INSERT INTO Elect_SubB1_Daily

( Date_Stamp )

SELECT Elect_SubB1_15Min.DateTime

FROM Elect_SubB1_15Min

WHERE ( datepart(hour, Elect_SubB1_15Min.DateTime ) = 0 ) AND

( datepart(minute, Elect_SubB1_15Min.DateTime ) = 0 )

go

declare @startdate datetime, @enddate datetime, @lastdate datetime, @startpeak datetime, @endpeak datetime, @firstdate datetime, @nullchecker int

select @firstdate = dateadd(day, -7, getdate())

SELECT @lastdate = max( Elect_SubB1_Daily.Date_Stamp )

FROM Elect_SubB1_Daily

select @lastdate = dateadd(day, -1, @lastdate)

select @startdate = convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " +

datename(year,@lastdate) + " 00:00"),

@enddate = convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " +

datename(year,@lastdate) + " 23:45"),

@startpeak = convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " +

datename(year,@lastdate) + " 08:15"),

@endpeak = convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " +

datename(year,@lastdate) + " 20:00") ,

@nullchecker = Pk_Dmnd_On

from Elect_SubB1_Daily

where Date_Stamp = @lastdate

/*============================================

This is the actual code that I am trying to run. The code I posted earlier is just for simplicity, but it looks like I missed something in the process?

LutzM SSC Guru Points: 107049 More actions · Answer 8

I still don't understand.

What's the difference between @lastdate and convert(datetime, datename(month,@lastdate) + " " + datename(day,@lastdate) + " " + datename(year,@lastdate) + " 00:00") ?

If all you're trying to do is to "normalize" @lastdate, why not simply using

dateadd(dd,datediff(dd,0,@lastdate),0)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

techzone12 SSCommitted Points: 1798 More actions · Answer 9

You are right, the code (not mine) can be improved a lot.

Stuff I inherited. I will need to work on simplifying it. But for now

I want to make sure it can run.