March 31, 2009 at 12:53 am
Hi all,
I have data like 20070423142807 and now i want to convert it to datetime like 04/23/2007 14:28:07 (i.e. mm/dd/yyyy hh:mm:ss) - please suggest me earliest.
thanks
Jeet
March 31, 2009 at 2:47 am
may be this can help u..
create FUNCTION CONV(
@TIME VARCHAR(20)
)
RETURNS VARCHAR(20)
as
begin
SELECT @time=CONVERT(VARCHAR,(SUBSTRING(@TIME,5,2)+'/'+SUBSTRING(@TIME,7,2)+'/'+SUBSTRING(@TIME,1,4)+' '+
SUBSTRING(@TIME,9,2)+':'+SUBSTRING(@TIME,11,2)+':'+SUBSTRING(@TIME,13,2)),101)
RETURN @time
end
select dbo.conv('20070423142807')
March 31, 2009 at 3:01 am
I wouldn't suggest handling this as varchar or similar: if you're dealing with dates, store it as a datetime column and format on the client side according to the user's regional settings.
To convert your string to datetime I'm afraid there's no ready-made format you can use to parse the date, you will have to convert it by hand:
declare @STR varchar(20)
declare @tmpdate datetime
set @STR = '20070423142807'
set @tmpdate = convert(datetime, left(@str,8), 112)
set @tmpdate = dateadd(hour, cast(substring(@str,9,2) as int), @tmpdate)
set @tmpdate = dateadd(minute, cast(substring(@str,11,2) as int), @tmpdate)
set @tmpdate = dateadd(second, cast(substring(@str,13,2) as int), @tmpdate)
select @tmpdate
To convert the value back to a well-known date format, you can use CONVERT again:
SELECT CONVERT(varchar(20), 120, @tmpdate)
If none of the formats shipped with the CONVERT function fits your needs, you could code a function to transform the date in whatever format you need, but I would suggest again formatting on the client side.
Regards
Gianluca
-- Gianluca Sartori
March 31, 2009 at 3:15 am
This is another way to format with a custom formatting string:
http://www.sqlservercentral.com/scripts/T-SQL+Aids/31111/
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply