Strip the time out from Datetime

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

SQL Server Development (2000)

Strip the time out from Datetime

Author

Topic

Tizita
Starting Member

12 Posts

Posted - 2011-01-21 : 06:28:54

Hi all,

I want to know what would be the best way of Stripping the time out from Datetime without the query taking too long to run please.
I have a script that has a where clause:

where saledate BETWEEN @From AND @To

result =
2010-01-19 11:14:48.413

Thank you in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-21 : 07:08:28

DECLARE @From DATETIME,
@To DATETIME

SELECT @From = '20110119', @To = '20110120'

SELECT * FROM Table1 WHERE SaleDate >= @From AND SaleDate < @To

N 56°04'39.26"
E 12°55'05.63"

Tizita
Starting Member

12 Posts

Posted - 2011-01-21 : 07:55:23

I would like to strip the time out of the following result, 2010-01-19 11:14:48.413 e.g. I want the result to be 2010-01-19

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 07:59:07

select
convert(varchar(10),saledate,120) as saledate,
...
from ...
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

Tizita
Starting Member

12 Posts

Posted - 2011-01-21 : 09:46:20

Thank you so much for your help, I used
select
convert(varchar(10),saledate,120) as saledate and it works fine.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 10:07:25

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-21 : 13:16:54

Don't say you are using that datetime string in your WHERE clause?
You will render your index on that column useless...

N 56°04'39.26"
E 12°55'05.63"

Tizita
Starting Member

12 Posts

Posted - 2011-01-21 : 14:47:03

quote:
Originally posted by Peso

Don't say you are using that datetime string in your WHERE clause?
You will render your index on that column useless...

N 56°04'39.26"
E 12°55'05.63"

I am using the Datetime string in the where clause because I'm trying to gate Sales that occurred between specific dates.

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-21 : 15:26:02

Then use my suggestion with open-ended search criteria. If you have an index over the SaleDate, your query will be really fast.
When you convert the SaleDate value to a string, no index will help you and your query will be slow.

N 56°04'39.26"
E 12°55'05.63"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 19:41:32

Not to mention 1/19/2010 00:00:00.000 is much different than 1/19/2010 11:59:59.000

Peso's is not only way faster due being sargeable, but is a lot less likely to yield incorrect results for a date range that includes timestamps.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources