Query and Extract
in [Programming SQL]
|
Prev: The multi-part identifier "dbo.v_dtf_SCOpenCallsTech.preffullname" could not be bound.
Next: Temp Table Context Problem In SP called by trigger?
From: dankonsoer on 14 Jun 2010 08:39 Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, Level 16, State 1, Line 1 Invalid object name 'AssetQuote'. I got this once before but cleared the table out because I wanted to change the date format. After I finally get those, I want to display the MONTHLY quote on the home page of the website, but after the 30/31st of every month, I was hoping to get this quote to automatically change! //declare the SQL statement that will query the database $query = "SELECT * "; $query .= "FROM quotes"; I'm sure it's some sort of WHERE statement, but I'm not sure how to obtain that? THANK YOU IN ADVANCE!!
From: Dan on 14 Jun 2010 08:47 "dankonsoer" <u60910(a)uwe> wrote in message news:a9838cc926f43(a)uwe... > Hello all, > > This seems basic, but I just cannot get it. While using SQL Server 2005 > and > the Microsoft SQL Server Management Studio, I created a database called > AssetQuote. Inside I have on table called assetquotes. > > From there, I have three columns, (date, quote, author) > > The column type for date is datetime and the other two are just text. > > My Query is something this: > > INSERT INTO AssetQuote (date, quote, author) > VALUES('052010','No act of kindness, no matter how small, is ever > wasted.', > 'Aesop'); > > When I run the query I get this result: > > Msg 208, Level 16, State 1, Line 1 > Invalid object name 'AssetQuote'. You say the table is called assetquotes (note the s at the end), yet you are trying to insert to a table called AssetQuote (no s at the end). Have you got the DDL for your table so it's clearer as to whether you have a typo or not? Also, is your database using a case-sensitive collation? If so, table and column names in statements will need to match the defined case or else you'll see the same error message. > > I got this once before but cleared the table out because I wanted to > change > the date format. > > After I finally get those, I want to display the MONTHLY quote on the home > page of the website, but after the 30/31st of every month, I was hoping to > get this quote to automatically change! > > //declare the SQL statement that will query the database > $query = "SELECT * "; > $query .= "FROM quotes"; > > I'm sure it's some sort of WHERE statement, but I'm not sure how to obtain > that? > > THANK YOU IN ADVANCE!! > And now in that query you're using the table name quotes. That's 3 different table names you've used in a single post. So what is the actual table name? It is assetquotes, AssetQuote, or quotes? As to the where, you would do this based on the date - but without knowing whether your data column is defined as a date or a varchar/char and how you want the date to be handled - does the date specify the start date for the quote to show, or the end date, or something else - it makes it awkward to suggest anything. -- Dan
From: dankonsoer on 14 Jun 2010 08:59 Thank you. I figured the first part out, it was something stupid on my part, but now the facts are: My table name is called assetquotes (all lowercase) my three columns are date, author, quote (all lowercase) I changed the date format to read 20100501 20100601 20100701 etc. Right now on my screen, I was able to print the current date using PHP, so it shows 201006, but the rest of the screen displays the full date/time as well as the quotes as I formatted it. 201006 May 1 2010 12:00AM '' No act of kindness, no matter how small, is ever wasted. '' Aesop Jun 1 2010 12:00AM '' When I want to speak, let me think first: Is it true? Is it kind? Is it necessary? If not, let it be left unsaid. '' Babcock Jul 1 2010 12:00AM '' There is more to life than just increasing its speed. '' Ghandi Now I just need to figure out how to format this query to get only the JUNE quote to display until July 1 when the next quote will appear! Thank you!
From: Dan on 14 Jun 2010 09:07 "dankonsoer" <u60910(a)uwe> wrote in message news:a983b9e5ff78b(a)uwe... > Thank you. I figured the first part out, it was something stupid on my > part, > but now the facts are: > > My table name is called assetquotes (all lowercase) > > my three columns are date, author, quote (all lowercase) > > I changed the date format to read 20100501 20100601 20100701 etc. > > Right now on my screen, I was able to print the current date using PHP, so > it > shows 201006, but the rest of the screen displays the full date/time as > well > as the quotes as I formatted it. > > 201006 > May 1 2010 12:00AM '' No act of kindness, no matter how small, is ever > wasted. > '' > > > Aesop > Jun 1 2010 12:00AM '' When I want to speak, let me think first: Is it > true? > Is it kind? Is it necessary? If not, let it be left unsaid. '' > > Babcock > Jul 1 2010 12:00AM '' There is more to life than just increasing its > speed. > '' > > Ghandi > > Now I just need to figure out how to format this query to get only the > JUNE > quote to display until July 1 when the next quote will appear! > > Thank you! > SELECT TOP 1 author, quote FROM assetquotes WHERE date < getdate() ORDER BY date DESC this will retrieve only the author and quote columns from the first row where the date column is less than or equal to the current system date/time. This assumes that the date column has a date datatype. -- Dan
From: dankonsoer on 14 Jun 2010 09:18
That is exactly what I needed! Thank you so much!! Worked like a charm! |