| Author |
Topic |
|
Tambam
Starting Member
14 Posts |
 Posted - 2011-02-23 : 14:15:33
|
| Hi, I am trying to use a query to return data within two dates but the query is returning nothing at all. Please could someone show me where I'm going wrong? ThanksSELECT *FROM SalePropertyWHERE DatePlaced BETWEEN 30/04/2007 AND 01/07/2007; |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-02-23 : 14:30:26
|
| Perhaps you don't have data for that time period?Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 14:31:19
|
| I thought that to, but I checked and I do :S |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-23 : 14:39:14
|
| Single quotes around the dates?JimEveryday I learn something that somebody else already knew |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 14:41:27
|
| I've tried that to :/ I get a "Data type mismatch in criteria expression" error message :( |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-23 : 14:54:14
|
| Without an DDL and DML it's hard to say. Is the DatePlaced column a DATE data type?Maybe start with using an ISO formatted date instead of a regional format? |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 14:55:06
|
| Yeah, it's a Date/time data type. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-23 : 14:56:30
|
| provide the date in same format as it is stored e.g. the default one look like '2007-04-01'CheersMIK |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 14:58:40
|
| SELECT *FROM SalePropertyWHERE DatePlaced BETWEEN '30/04/2007' AND '01/07/2010';Data type: Date/Time |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-23 : 15:02:21
|
use this one  :SELECT *FROM SalePropertyWHERE DatePlaced BETWEEN '04/30/2007' AND '07/01/2010';CheersMIK |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 15:04:27
|
| Same problem :/ |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-23 : 15:09:33
|
| SQL doesn't store dates like '30/04/2007', are you sure that dateplaced is a datetime data type, and that it doesn't just look like a date? Just for jokes, try thisSELECT *FROM SalePropertyWHERE convert(datetime,DatePlaced) BETWEEN '20070430' AND '20100107';JimEveryday I learn something that somebody else already knew |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 15:15:44
|
| The resultant code gives a Undefined function 'CONVERT' in expression error message. |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 15:27:49
|
| Using > and > on one of the dates is working it's just implementing the BETWEEN statement for both of them. |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 15:31:57
|
| * > and < |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-23 : 15:36:51
|
| Are you using Microsoft SQL Server t-SQL?JimEveryday I learn something that somebody else already knew |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 15:38:13
|
| No, Access 2007 haha. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-23 : 15:44:37
|
quote:
Originally posted by MIK_2008
provide the date in same format as it is stored e.g. the default one look like '2007-04-01'
Just for educational purposes, dates are not stored in a format (at least in SQL Server) just an offset. Here is an old article that goes into greater detail:http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx |
|
|
|
Tambam
Starting Member
14 Posts |
Posted - 2011-02-23 : 16:36:17
|
| Sorted, did some fiddling and this works:SELECT *FROM SalePropertyWHERE DatePlaced Between #4/30/2007# And #7/1/2007#;Thanks for your time :D |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Date range query not returning anything