Post reply

T-SQL query giving errors in SQL 2008

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

  • December 16, 2010 at 1:11 pm

    #1263676

    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]

  • December 16, 2010 at 1:12 pm

    #1263677

    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.

  • December 16, 2010 at 1:12 pm

    #1263678

    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

  • December 16, 2010 at 1:15 pm

    #1263681

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

  • December 16, 2010 at 1:22 pm

    #1263685

    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

  • December 16, 2010 at 1:25 pm

    #1263687

    CAn you post your final solution.....

    thank you

  • December 16, 2010 at 1:31 pm

    #1263692

    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?

  • December 16, 2010 at 1:48 pm

    #1263710

    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]

  • December 16, 2010 at 5:10 pm

    #1263805

    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.

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

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