Can't figure out how to correctly input a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can't figure out how to correctly input a date

Author

Topic

TheMrPatrick
Starting Member

4 Posts

Posted - 2012-09-21 : 14:51:27

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 14:56:00

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.

TheMrPatrick
Starting Member

4 Posts

Posted - 2012-09-21 : 15:17:37

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 15:54:25

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

TheMrPatrick
Starting Member

4 Posts

Posted - 2012-09-21 : 16:33:01

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.

TheMrPatrick
Starting Member

4 Posts

Posted - 2012-09-21 : 17:35:11

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources