Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Problem with Date Query |
Sun, Feb 7 2010 6:11 PM | Permanent Link |
Dale Derix | I've got to be missing something obvious here. Shouldn't this return records with the
date of Feb, 7, 2010? Instead it returns nothing. select * from contacts where ctDateCreated >= DATE '2010-02-07' and ctDateCreated <= DATE '2010-02-07' Dale |
Sun, Feb 7 2010 10:05 PM | Permanent Link |
"Raul" | What's the data type for the crDateCreated? Is it "Date" or is it something
like Timestamp ? Raul "Dale Derix" <dale@emerald-data.com> wrote in message news:1D517277-03AC-4618-9B30-867256D8E9ED@news.elevatesoft.com... > I've got to be missing something obvious here. Shouldn't this return > records with the > date of Feb, 7, 2010? Instead it returns nothing. > > select * from contacts > where ctDateCreated >= DATE '2010-02-07' and ctDateCreated <= DATE > '2010-02-07' > > Dale > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4845 (20100207) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 4845 (20100207) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
Mon, Feb 8 2010 10:10 AM | Permanent Link |
Dale Derix | <<<< "Raul" wrote:
What's the data type for the crDateCreated? Is it "Date" or is it something like Timestamp ? Raul >>>>>>> Ahhh... that's it! Changed it to this and it now works. select * from contacts where ctDateCreated >= TIMESTAMP '2010-02-07 00:00' and ctDateCreated <= TIMESTAMP '2010-02-07 23:59' Definitely a newbie error. Thanks, Dale |
Mon, Feb 8 2010 10:37 AM | Permanent Link |
"Raul" | No problem.
If you want to avoid having to include the time every time then either separate the timestamp into 2 columns : one for date and one for time (it is extra work on updating them but you can have edb update them thru triggers for example automatically) or try using next day in SQL with the strict less than comparison (e.g. where ctDateCreated >= DATE '2010-02-07' and ctDateCreated < DATE '2010-02-07' ). Raul "Dale Derix" <dale@emerald-data.com> wrote in message news:18FF132F-512A-4D9F-B078-D501EDF9AAFA@news.elevatesoft.com... > <<<< "Raul" wrote: > > What's the data type for the crDateCreated? Is it "Date" or is it > something > like Timestamp ? > > Raul >>>>>>>> > > Ahhh... that's it! > > Changed it to this and it now works. > > select * from contacts > where ctDateCreated >= TIMESTAMP '2010-02-07 00:00' and ctDateCreated <= > TIMESTAMP > '2010-02-07 23:59' > > Definitely a newbie error. > > Thanks, > > Dale > > |
Mon, Feb 8 2010 11:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dale
OR select * from contacts where CAST(ctDateCreated AS DATE) = DATE '2010-02-07' Roy Lambert [Team Elevate] |
Mon, Feb 8 2010 12:50 PM | Permanent Link |
Dale Derix | <<<<<
select * from contacts where CAST(ctDateCreated AS DATE) = DATE '2010-02-07' Roy Lambert [Team Elevate] >>>>> Hi Roy: Actually, the query allows a user to seach for contacts by a range of dates..... My surprise was when it didnt work when the start and end dates were the same. I thought about a solution such as yours but then I would have two queries, one if the start and end date was the same date, and the other for a daterange situation. The value is actually stored as a TimeStamp, so I changed the query to search from 12:00 am on the start day to midnight on the end day. Seems to be working fine now. Dale |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |