datetime manipulation
-
hello daomings,
i had this problem and i used convert and cast functions to solve this.may be this is not the best solution but try this.and if u get any thing better than this pls.. let me know.
convert(varchar(12),[EM_dtJoin],114) for split the time and
cast(convert(varchar(10),[EM_dtJoin],103) as datetime) for the date
regards..
amal
Kindest Regards,Amal Fernando
94-0717-318631 http://spaces.msn.com/members/amalatsliit/ -
See this article by SQL Server MVP Tibor Karaszi for an extensive explanation of the datetime datatype and how to solve problems like this one: http://www.karaszi.com/SQLServer/info_datetime.asp
-
hello daomings,
I'm using int column to store date part using following:
SET @event_date = DATEDIFF(dd, 0, getdate())
when I'm retrieving it (through stored proc) I convert it back:
CONVERT(varchar(20), cast(@event_date as datetime), 101).
Hope it helps.
Vadim Svinkin.
-
Try this:
CREATE FUNCTION RemoveTimeFromDate (@DateToModify datetime)
--input like: 01/01/2003 12:34:56
--output: 01/01/2003 00:00:00
--Ian Stone December 2003, issue1.0
RETURNS datetime
AS
BEGIN
DECLARE @ReturnDate datetime
SET @ReturnDate=DATEADD(hh,-DATEPART(hh,@DateToModify),@DateToModify)
SET @ReturnDate=DATEADD(mi,-DATEPART(mi,@ReturnDate),@ReturnDate)
SET @ReturnDate=DATEADD(ss,-DATEPART(ss,@ReturnDate),@ReturnDate)
SET @ReturnDate=DATEADD(ms,-DATEPART(ms,@ReturnDate),@ReturnDate)
RETURN @ReturnDate
END
-
I think that this would be a simpler version :
CREATE FUNCTION dbo.RemoveTime (@DateTime as datetime)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d, 0, datediff(d, 0, @DateTime))
END
GO
Select dbo.RemoveTime (GetDate())
drop function RemoveTime
-
These are a bit dirty and make use of implicit conversions but they are probably pretty efficient:
create function dbo.dateportion (@d as datetime)
returns datetime
as
begin
return floor(cast(@d as float))
end
create function dbo.timeportion (@d as datetime)
returns datetime
as
begin
return @d - floor(cast(@d as float))
end
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply