Altering Date Time - 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 2000 Forums

SQL Server Development (2000)

Altering Date Time

Author

Topic

tmh
Starting Member

1 Post

Posted - 2002-09-13 : 01:13:59

Lets say I have a date
09/13/02 1:12:06 AM

Lets say I wanted to erase time off the date and replace
it with
7:00:00 PM

So I would have
09/13/02 7:00:00 PM

How can I go about doing this?

Thanks,

Rob Taylor
www.takemehunting.com

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-09-13 : 01:52:18

sounds like you need to go back to convert 101, (hey that's funny)

try (I've used getdate() - but you would put your date)

select convert(datetime, convert(nvarchar(20),getdate(),101) + ' 7:00:00 PM')

-Check Convert in Books On Line-

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Southpaw
Starting Member

6 Posts

Posted - 2002-09-13 : 11:59:37

SELECT DATEADD(hour, -DATEPART(hour, GETDATE()), DATEADD(minute, -DATEPART(minute, GETDATE()), DATEADD(second, -DATEPART(second, GETDATE()), DATEADD(millisecond, -DATEPART(millisecond, GETDATE()), GETDATE()))))

Replace GETDATE() with the column name and you've got yourself a date without the time. Then you can use DATEADD to add 7 hours to that.

-- Southpaw

sanjig
Starting Member

1 Post

Posted - 2002-09-13 : 12:37:07

quote:

sounds like you need to go back to convert 101, (hey that's funny)

try (I've used getdate() - but you would put your date)

select convert(varchar(20),getdate(),101) + ' 7:00:00PM'

Jigna

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-13 : 12:40:28

quote:

SELECT DATEADD(hour, -DATEPART(hour, GETDATE()), DATEADD(minute, -DATEPART(minute, GETDATE()), DATEADD(second, -DATEPART(second, GETDATE()), DATEADD(millisecond, -DATEPART(millisecond, GETDATE()), GETDATE()))))

urgh!

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources