Remove time from date field - 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

General SQL Server Forums

New to SQL Server Programming

Remove time from date field

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-12 : 10:15:39

I'm using the following to split the time portion of a date time field and move that time into it's own "TIME" column:



Update offense.dbo.OFF_NAR
set SUPTIME = convert(varchar(5), SUPDATE, 114)

now i need to remove that time value from the Date field. I'm assuming a substring function won't work because the datatype is datetime. If i cast the field as a varchar then run the substring, will that work or is there a better way?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-12 : 10:25:25

[code]dateadd(day, datediff(day, 0, SUPDATE), 0)[/code]

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-12 : 10:28:19

UPDATE offense.dbo.OFF_NAR SET
SUPTIME=DATEADD(day, DATEDIFF(day, SUPDATE, 0), SUPDATE),
SUPDATE=DATEADD(day, DATEDIFF(day, 0, SUPDATE), 0)

BillTalada
Starting Member

1 Post

Posted - 2012-09-12 : 10:44:58

[code]
-- truncate time from datetime
-- always works regardless of @@langid or @@datefirst settings
DECLARE @CreatedDate datetime;
SET @CreatedDate = (select cast(convert(varchar(8), getdate(), 112) as smalldatetime));
SELECT @CreatedDate;
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 10:57:23

if above sql 2008 this will also work

CONVERT(date,SUPDATE)

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-12 : 14:37:47

Wow. Holy crap. Ok. The script i wound up using was:

convert(varchar(10), DATE, 101)

Given to me by my supervisor.

Ugh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:44:33

quote:
Originally posted by WJHamel

Wow. Holy crap. Ok. The script i wound up using was:

convert(varchar(10), DATE, 101)

Given to me by my supervisor.

Ugh.

not a good thing to change datatype

especially if you want to do date manipulations later using these converted values

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:23:15

There can be several ways to do this
http://beyondrelational.com/modules/2/blogs/70/Posts/17608/different-ways-to-remove-time-part-from-datetime-values-faster-methods.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources