SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can't figure out how to correctly input a date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TheMrPatrick
Starting Member

Netherlands
4 Posts

Posted - 09/21/2012 :  14:51:27  Show Profile  Reply with Quote
Hello, I am completely new to SQL and I am working on assignments for SQL now. All went smoothly, but now I'm stuck. I am working in SimpelQL and I have to let it show fridges that were fixed on a specific date. My working code so far:
SELECT * FROM controleur, controle, apparaat
WHERE apparaattype = "koelkast"

I assume you have to add: AND controledatum = 13-04-2003. But the way the date is written in the database is something I don't understand. It is written as this: 13 apr 2003, but the data type is DATE. How do I write the WHERE command for date with "apr" in it? I have tried 13-04-2003, I don't get an error but no fridges are being shown.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  14:56:00  Show Profile  Reply with Quote
Assuming the data type is DATE as you indicated, what you are seeing when you query is really a string representation of the date. You can add the where clause in the query in any other format that SQL Server can understand. Using an unambiguous date format such as YYYYMMDD format would be most appropriate. So if your where clause is the following, it should work correctly.
WHERE controledatum = '20030413'
Now, if your data type is DATETIME, or something else, then that complicates matters - but since you found that it is DATE, you wouldn't need to worry about those at this time.
Go to Top of Page

TheMrPatrick
Starting Member

Netherlands
4 Posts

Posted - 09/21/2012 :  15:17:37  Show Profile  Reply with Quote
So I type this:
SELECT * FROM controleur, controle, apparaat
WHERE apparaattype = "koelkast" AND controledatum = 20030417 (date is actually 17 instead of 13)
But it still doesn't work, nothing shows up. I have also tried 17042003 because the dates are written like this in the table, the EU standard, but still nothing. There are fixed fridges with this date.
When I look in metadata it does show that the datatype is "DATE". But when I try the command with the '' you added to the date I get an error saying: "Data Type mismatch in criteria expression"(google translate). I have noticed that there is an option on how the date is shown in the table.

Edited by - TheMrPatrick on 09/21/2012 15:19:06
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/21/2012 :  15:54:25  Show Profile  Reply with Quote
Are you using Microsoft SQL Server? If so, you should enclose the literals in single quotes - for example like this:
SELECT *
FROM   controleur
WHERE  apparaattype = 'koelkast'
       AND controledatum = '20030417'
Notice that I have listed only one table there. If you have more than one table, you need to "join" them. Take a look here to see how you do the joins:
http://www.w3schools.com/sql/sql_join.asp
Go to Top of Page

TheMrPatrick
Starting Member

Netherlands
4 Posts

Posted - 09/21/2012 :  16:33:01  Show Profile  Reply with Quote
I don't know what kind of server I'm using. I just load a database I got with the assignment in the program S(imple)QL, which I also got with the assignment. But I assume I'm not using microsoft SQL since these single quotes give me an error. And I have no problem working with multiple tables. But part of the assignment is, is that I am not allowed to use certain commands including JOIN.

But when I use: SELECT * FROM controleur, controle, apparaat . I get to see all tables without any problem including the dates. It's just that I can't seem to find a way to only show the records which have this specific date in them.
Go to Top of Page

TheMrPatrick
Starting Member

Netherlands
4 Posts

Posted - 09/21/2012 :  17:35:11  Show Profile  Reply with Quote
I have finally found out what I did wrong. Instead of quotation marks I had to write the date like this: AND controledatum = #17-04-2003#. But thanks for the help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000