getting rid of hours, minutes and seconds

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)

getting rid of hours, minutes and seconds

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-08-14 : 10:05:17

in a datetime column.
Hi everyone,
I need a table with a datetime column to compare various dates to 1-august-2008 for example , so i can write something like:


if mydate='20080801'

I created the following table:


create table mytable
(
 mydate datetime
)
insert mytable
select
convert(char(8),getdate(),112)

but "mydate" still contains hour+minutes+seconds. How do i get rid of it?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 10:08:07

[code]select dateadd(day, datediff(day, 0, getdate()), 0)[/code]

datetime data type always contain the date and the time. The above method just set the time to 00:00:00

KH
[spoiler]Time is always against us[/spoiler]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 10:09:34

datetime data type always has time aswell (unless you use 2008, then you can have DATE data type)

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 10:09:34

Dont worry about time part

Use

Where date_col>=@mydate and date_col<dateadd(day,1,@mydate)

If @mydate contains time part

Where date_col>=dateadd(day,datediff(day,@mydate,0),0) and date_col<dateadd(day,datediff(day,@mydate,0),1)

Madhivanan

Failing to plan is Planning to fail

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-08-17 : 05:57:05

quote:
Originally posted by khtan

select dateadd(day, datediff(day, 0, getdate()), 0)

datetime data type always contain the date and the time. The above method just set the time to 00:00:00

KH
[spoiler]Time is always against us[/spoiler]

thanks

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-08-17 : 05:58:12

quote:
Originally posted by madhivanan

Dont worry about time part

Use

Where date_col>=@mydate and date_col<dateadd(day,1,@mydate)

If @mydate contains time part

Where date_col>=dateadd(day,datediff(day,@mydate,0),0) and date_col<dateadd(day,datediff(day,@mydate,0),1)

Madhivanan

Failing to plan is Planning to fail

and thanks again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources