strip houres from date - 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 2005 Forums

Transact-SQL (2005)

strip houres from date

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-03-13 : 09:01:04

Hi:)

I wonder, can someone pls give me the TSQL code, for following problem.

I have a Date like : 2007-01-19 11:25:32.093

But I only need the 2007-01-19.
I know its something with Datediff, but I am a beginner :)

Ty in advanced

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-13 : 09:07:21

[code]SELECT Dateadd(dd,0,Datediff(dd,0,'2007-01-19 11:25:32.093')) [/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 09:07:30

[code]select dateadd(dd,datediff(dd,0,current_timestamp),0)[/code]

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-13 : 09:08:16

select convert(varchar(10), getdate(), 110)

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 09:15:25

heavymind - that gives date - I think you meant

select convert(varchar(5), getdate(), 114) --truncated to show just hours and minutes
select convert(varchar(12), getdate(), 114) --full time

http://www.sql-server-helper.com/tips/date-formats.aspx

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-13 : 09:20:32

quote:
Originally posted by darkdusky

heavymind - that gives date - I think you meant

select convert(varchar(5), getdate(), 114) --truncated to show just hours and minutes
select convert(varchar(12), getdate(), 114) --full time

http://www.sql-server-helper.com/tips/date-formats.aspx

quote:
But I only need the 2007-01-19.

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 10:32:42

My mistake!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-03-13 : 10:42:37

The dateadd variants given earlier are more efficient. Use them.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 11:18:47

Blindman - I tested the 2 methods and convert is faster.
I tried looping through outputing SELECT, INSERT to new table and populating a variable.
CONVERT is faster every time.
Try it yourself:

declare @D datetime
set @D = getdate()

SET nocount ON
DECLARE @rows INT , @row INT, @count INT , @Str varchar (max)
SELECT @rows = 10000000
SELECT @row = 0
SELECT @count = 0
set @Str=''
WHILE @row < @rows
BEGIN

Set @Str= convert(varchar(10), @D,110) --38 seconds
--Set @Str= Dateadd(dd,0,Datediff(dd,0,@D)) --40 seconds
SELECT @row = @row + 1
END
SELECT @Str

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 12:06:55

Itzik Ben-Gan has a nice series of articles here on DATETIME

Start at article 1
http://www.sqlmag.com/Article/ArticleID/94487/sql_server_94487.html

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-03-13 : 12:08:20

<Sigh.....>
That is because you are doing an additional implicit convert when you are assigning a valid datetime value to a string variable. Try this, and you will get very different results:

declare @D datetime
set @D = getdate()

SET nocount ON
DECLARE @rows INT , @row INT, @count INT , @NewDate datetime
SELECT @rows = 10000000
SELECT @row = 0
SELECT @count = 0
WHILE @row < @rows
BEGIN

Set @NewDate= convert(varchar(10), @D,110) --36 seconds
--Set @NewDate = Dateadd(dd,0,Datediff(dd,0,@D)) --33 seconds

SELECT @row = @row + 1
END

Accurate results require accurate tests.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 12:15:16

Here is how you should test.
http://www.sqlmag.com/article/articleid/100884/sql_server_blog_100884.html

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-13 : 12:22:38

Blindman you are right though I applied your logic and did not force the varchar back into a datetime either. So to be fair I put the CONVERT into a varchar and the DATEADD into a datetime.
You still win (grrr). The times on my PC are different but the margin closed from 22sec(Convert): 14sec(DATEADD) to 17sec(Convert) : 14sec(DATEADD)

declare @D datetime
set @D = getdate()

SET nocount ON
DECLARE @rows INT , @row INT, @count INT , @NewDate datetime, @NewVar varchar (50)
SELECT @rows = 10000000
SELECT @row = 0
SELECT @count = 0
WHILE @row < @rows
BEGIN

Set @NewVar= convert(varchar(10), @D,110) --17 seconds
--Set @NewDate = Dateadd(dd,0,Datediff(dd,0,@D)) --14 seconds

SELECT @row = @row + 1
END

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-03-13 : 12:26:17

Granted, there is not a huge difference.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 12:30:31

In the link above Itzik showed the DATEADD/DATEDIFF is three times faster than CONVERT.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources