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.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2004-08-08 : 08:47:44
|
| I have a date in the following format : 08/06/2004 00:47:47stored in a char field. What sql would I do to look between 2 dates. --- say I wanted to select all entries between 08/01/2004 and 08/10/2004.Will between work if it is not a date field in the sql? |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-08 : 08:55:09
|
BETWEEN will work on a char field, but not the way you think it should. Look at this example:USE NORTHWINDGOSELECT * FROM products WHERE productname BETWEEN 'ikura' AND 'mishi' The BETWEEN will work on the ASCII values of the data in the columns, but you want them to be compared as dates. In that case, you can use the CAST or CONVERT functions to convert the data into the datatype you want and get the behaviour you want. See the Books Online for more details on these two functions. Your sql statement would probably be:SELECT * FROM myTable WHERE CAST(DateColumn AS Datetime) BETWEEN '20040801' AND '20040901' Note that I used the ANSI form of dates "yyyymmdd", that is the most un-ambiguous format and you should try to use that where possible, because a date like '08/01/2004' is eighth of January in the UK, and first of August in the US.OS |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-08-08 : 09:51:30
|
| thanks and how would I check between date and time? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-08 : 10:03:35
|
| Do you mean checking for values that fall between two datetimes? a date without a time value is still a datetime, the time part defaults to midnight (00:00:00). You can include times in the comparision if you want to:SELECT * FROM myTable WHERE CAST(DateColumn AS Datetime) BETWEEN '20040801 12:00:00' AND '20040901 12:00:00'Or did you mean looking for values within a specific time frame? Like all records with datetime value between 6 AM and 7 PM? Look up DATEPART function in the Books Online for that.OS |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-08-09 : 05:10:59
|
tried your query:SELECT * FROM mytable WHERE cast(time as datetime) between '20040402' and '20040920';In this table I have one record with a time (character field) of 08/06/2004 00:47:47.The query executes but does not find the record. Why not? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-09 : 05:15:11
|
Time is a keyword in SQL. You could try wrapping it in square brackets to ensure that it is picking up the column. SELECT * FROM mytable WHERE cast([time] as datetime) between '20040402' and '20040920'; Are you sure that the row exists exactly as you think it does? Can you post some sample data? P.S. this is SQL Server, right? the semi-colon at the end reminds me of Access.OS |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-08-09 : 05:33:22
|
| no this gave me an error. This is in mysql. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-08-09 : 06:02:19
|
| oh dear! I'm afraid my knowledge of mysql is fairly limited. This forum specializes in SQL Server, so you'll probably get better answers elsewhere. Try http://www.dbforums.com/f5OS |
|
|
|
|
|
|
sql date help