datetime manipulation
-
I'm working with the daytime type data. Is there any way to retrieve only the date part and time part seperately and then put them in another column with datetime type?
for example:
column1(datetime) -> 2005-02-01 00:00:00.000
column2(datetime) -> 2005-06-07 12:03:45.000
put date from column1 and time from column2 together so column3(datetime) will be -> 2005-02-01 12:03:45.000
-
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply