Strip off Milliseconds in Where Clause

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 2008 Forums

Transact-SQL (2008)

Strip off Milliseconds in Where Clause

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-07-22 : 13:10:52

How do I strip off a dates miliseconds when used in where clause? Basically, I want to delete all records from tableA where the create date = the date without miliseconds in tableB

begin transaction
delete from tableA
where create_ts = (select top 1 create_ts from tablEB)

Dates are stored in the table as DateTime (2011-07-21 08:41:08.093)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-22 : 13:20:42

dateadd(ss,datediff(ms,dateadd(dd,datediff(dd,0,create_ts),0),create_ts)/1000,dateadd(dd,datediff(dd,0,create_ts),0))

will strip off milliseconds part from create_ts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-07-25 : 08:57:55

Thanks Visakh16,

What is I now want to strip away the time from the current date?

Current = '2011-07-17 11:00:37.000'

Stripped = '2011-07-17'

Thanks
Q

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-07-25 : 08:58:47

Sorry for my english... I meant to say.... "What if I now want to...."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:11:36

thats much easier

SELECT DATEADD(dd,DATEDIFF(dd,0,@Date),0)

will strip off time part from @date where variables contains a date value

see below for more info
http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-07-25 : 13:20:03

This is cool, but I notice a date displays as "2011-07-25 00:00:00.000" when running the query.

How can the time stamp be truely displayed, or return as "2011-07-25"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 13:47:15

datetime would have both date and timepart hence 00:00:00 etc if you want to show as date alone without 00:00:00 you need to make it varchar and trip by first 10 characters using convert(varchar(10),datevalue,121) or if in sql 2008 or above cast it to new DATE datatype.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-25 : 16:14:43

DELETE FROM dbo.Table1
WHERE Col1 >= '20110725' AND Col1 < '20110726'

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

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-07-26 : 07:57:22

Thanks, I was able to use cast. and ended up with this:

delete from TABLE1
where convert(char(10),create_ts,111) = (select top 1 convert(char(10),create_ts,111) from TABLE2)

Works for me....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-26 : 09:40:22

Yes, but you will KILL your server and force a table scan of ALL your data in the table.
Not so very clever done.

If you want to use an index (if present, of course) follow my advice.

Also, if I may point out, you have no ORDER BY in your subquery so there is NO guarantee SQL Server will get the lastest date for you. If you are using ONLY TOP(1), SQL Server is free to get ANY date back to you. It depends on a number of factors, but the bottom line is that there is no guaranteed physical order in the rows in a table. Only a logical order, which you can enforce by adding an ORDER BY.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-26 : 09:50:42

Here is a proper written piece of code

DECLARE	@ts DATETIME

SELECT TOP(1)	@ts = create_ts
FROM		dbo.Table2
ORDER BY	create_ts DESC

SET	@ts = DATEADD(DAY, DATEDIFF(DAY, '19000101', @ts), '19000101')

DELETE
FROM	dbo.Table1
WHERE	create_ts >= @ts

I tested and tried it on a table with 120,000,000 records and it took 2 seconds and deleted 151,833 rows. Meanwhile I wrote my previous post I also started to test your method and it took 19 minutes and 21 seconds. Same 151,833 rows deleted.

I am not in a position to tell you what you should use. I can only tell you from my experience what is better for you.
It's up to you to listen. The rest is your choice.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 12:59:52

no need to cast it to varchar for doing comparison in delete. even if it has timepart 00:00:00 it will work fine. So please go with Peso's suggestion as its optimized. the casting has to done only for display purpose especially when it cant be done at front end at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources