Author |
Topic |
Lemmy44
Starting Member
20 Posts |
Posted - 2005-09-23 : 14:47:35
|
OK, I've got one here that is probably pretty easy, but is giving me fits and I can't find an example to compare it with.Here is the line that I am working with:WHERE contact.contact_date = '09-07-2005'The actual date will end up being a user-input parameter. The contact_date's all have times as well, but we don't want the users to have to enter the times, only the date.What I need to do, is be able to find any contact_date's that occur on the date given, regardless of the time.I know I've seen this done before, but I for the life of me can't find any reference to it.Thanks, |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-23 : 14:53:52
|
Here are a few alternatives:where datediff(day, contact.contact_date, '09-07-2005') = 0where contact.contact_date >= '09-07-2005'and contact.contact_date < '09-08-2005'where dateadd(day, datediff(day, 0, contact.contact_date), 0) = '09-08-2005' Be One with the OptimizerTG |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-23 : 14:56:27
|
If the database design is something you can change, you might want to consider keeping your dates and times in separate columns:http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx |
|
|
Lemmy44
Starting Member
20 Posts |
Posted - 2005-09-23 : 15:00:26
|
Sweet. Thanks for the replies.Makes the Friday afternoon run that much smoother.Cheers. |
|
|
rheitzman
Starting Member
16 Posts |
Posted - 2005-09-23 : 17:19:43
|
If portability is an issue use '2005-09-07' instead of '09-07-2005' |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-24 : 00:30:08
|
"Here are a few alternatives"where contact.contact_date >= '09-07-2005' and contact.contact_date < '09-08-2005' will be more efficient than the other two methods if there is an index on contact.contact_date"If portability is an issue ..."Needs to be '20050907' (without the hyphens). The hyphens won't work on all locales - beats me why, but that's been my experience.Kristen |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-24 : 00:46:06
|
How about this?where contact.contact_date >= '20050907' and contact.contact_date < '20050908'>>Needs to be '20050907' (without the hyphens). The hyphens won't work on all locales - beats me why, but that's been my experience.Because it is an ISO Format MadhivananFailing to plan is Planning to fail |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-24 : 02:15:49
|
The ISO 8601 standards for dates and times include dashes:http://www.iso.org/iso/en/prods-services/popstds/datesandtime.htmlCalendar date: YYYY-MM-DDExample: 2003-04-01 represents the first day of April in 2003. Week date: YYYY-Www-DExample: 2003-W14-2 represents the second day of the fourteenth week of 2003.Time of the day: hh:mm:ssExample: 23:59:59 represents the time one second before midnight.Date and time: YYYY-MM-DDThh:mm:ssExample: 2003-04-01T13:01:02 represents one minute and two seconds after one o'clock in the afternoon of 2003-04-01. This works OK for me:select [Date_time] = convert(datetime,'2003-04-01T13:01:02 ')Date_time ---------------------------------------2003-04-01 13:01:02.000(1 row(s) affected)quote: Originally posted by madhivanan How about this?where contact.contact_date >= '20050907' and contact.contact_date < '20050908'>>Needs to be '20050907' (without the hyphens). The hyphens won't work on all locales - beats me why, but that's been my experience.Because it is an ISO Format MadhivananFailing to plan is Planning to fail
CODO ERGO SUM |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-24 : 02:24:03
|
In BoL CONVERT(varchar, GetDate(), 112) is referred to as "ISO" and gives yyyymmdd format.The hyphens are OK if you use ISO8601 and include the time YYYY-MM-DDThh:mm:ssBut a yyyy-mm-dd string, on its own, is not safe on all locales IMbitterEKristen |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-24 : 02:34:13
|
To add to the confusion, BOL calls this ISO8601 format:select [ISO8601] = convert(varchar(30),getdate(),126)ISO8601 ------------------------------ 2005-09-24T02:31:39.003(1 row(s) affected)CODO ERGO SUM |
|
|
|