March 29, 2004 at 3:53 am
Hi,
Is there any function to seperate the Date and Time from DateTime field
for eg: i have to split "1/1/1994 6:46:00 PM" in to
1/1/1994 and 6:46:00 PM seperately
thanks
subban
March 29, 2004 at 4:35 am
Hello,
I would look up the Convert funtion is the Books on Line. Here is an example of how to use convert. Also, if you NEED it to be of datetime type, you can use convert to change it back to datetime after you have converted to varchar.
Here is an example for you.
select convert(varchar(8), getdate(), 112), convert(varchar(8), getdate(), 108)
Chuck
March 29, 2004 at 4:55 am
Hi,
This may work for you
select convert(varchar(11),getdate(),101) + ' and ' + RIGHT(convert(varchar(30), getdate(), 130),13)
Thanks
Prasad Bhogadi
www.inforaise.com
March 29, 2004 at 8:14 am
ok thanks....
subban
March 29, 2004 at 8:59 pm
I used this UDF alot to return only the date part. I got from some site. It is also possible to get the time part mathematically. I just forget the URL though!
CREATE FUNCTION fn_Date (
@ADate datetime )
RETURNS datetime AS
BEGIN
declare @DateVal datetime
set @DateVal = CAST(FLOOR(CAST(@ADate AS float)) AS datetime)
return @DateVal
END
AUXilliary COMmunication 1
March 30, 2004 at 2:38 am
There was a long thread on this topic recently. The concensus was that the most efficient way of stripping the time from a datetime value was :
DateAdd(d, 0, DateDiff(d, 0, GetDate()))
Running DateDiff on the result of this then can give you the time part.
March 30, 2004 at 5:33 am
Sorry your order is incorrect should read
DateAdd(d, DateDiff(d, 0, GetDate()), 0)
However that doesn't strip the time in reality just sets to 0 which is midnight but for calculations is the effect of stripping the time and many applications recognize midnight as no time value to display. But for the sake of the original question the folks using Convert using a convert format are right as to the best way to get the values sepeartely (at least as far as I have seen to date).
March 30, 2004 at 5:39 am
Oops!! Thanks.
March 30, 2004 at 5:54 am
To get the date, try using the following:
Cast(Left(@Date,11) as Datetime)
Replace @Date with your date variable.
March 31, 2004 at 7:06 am
Don't ever manipulate date through varchar without using a defined style, because it depends on the regional settings. This way it may or may not work on different servers.
Do it this way:
CONVERT(datetime, CONVERT(varchar, getdate(), 101), 101)
It doesn't matter if you use any style (101, 102, 103, 104, ...) as long as it's the one that shows only date and both conversions use the same style.
You can similarly use style 108 to keep only time, but since SQL Server has no time-only datatype you automatically get 1/1/1900 date apended.
Of course there is allways a way to stitch it together using DATEPART and DATEADD.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply