Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Date, regardless of time
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lemmy44
Starting Member

Canada
20 Posts

Posted - 09/23/2005 :  14:47:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 09/23/2005 :  14:53:52  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/23/2005 :  14:56:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Lemmy44
Starting Member

Canada
20 Posts

Posted - 09/23/2005 :  15:00:26  Show Profile  Reply with Quote
Sweet. Thanks for the replies.

Makes the Friday afternoon run that much smoother.

Cheers.
Go to Top of Page

rheitzman
Starting Member

16 Posts

Posted - 09/23/2005 :  17:19:43  Show Profile  Reply with Quote
If portability is an issue use '2005-09-07' instead of '09-07-2005'
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 09/24/2005 :  00:30:08  Show Profile  Reply with Quote
"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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 09/24/2005 :  00:46:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/24/2005 :  02:15:49  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 09/24/2005 02:22:57
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 09/24/2005 :  02:24:03  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/24/2005 :  02:34:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000