Date, regardless of time - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Date, regardless of time

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') = 0

where	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 Optimizer
TG

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

Madhivanan

Failing 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.html

Calendar date: YYYY-MM-DD
Example: 2003-04-01 represents the first day of April in 2003.

Week date: YYYY-Www-D
Example: 2003-W14-2 represents the second day of the fourteenth week of 2003.

Time of the day: hh:mm:ss
Example: 23:59:59 represents the time one second before midnight.

Date and time: YYYY-MM-DDThh:mm:ss
Example: 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

Madhivanan

Failing 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:ss

But a yyyy-mm-dd string, on its own, is not safe on all locales IMbitterE

Kristen

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources