April 17, 2008 at 8:24 am
hi
but i need to convert dates like o4 th july 2005 or
3rd august 1997
31st december 2000
22nd july 1997....i.e th,nd,rd,....
to default formate..... 04/07/2005,
03/08/1997
helpe with a function.....
i am trying this ,but it convert without th,rd,nd,...
alter FUNCTION [dbo].[GetPhoto](@Photo nvarchar(max),@UploadedDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @getdate-2 DATETIME
DECLARE @strDate varchar(24)
SET @getdate-2 = @UploadedDate
if NOT (@Photo IS NULL)
BEGIN
--RETURN @getdate-2
DECLARE @Startposition int
SET @Startposition= patindex('%Date:%', IsNull(@Photo, '') )+5
SET @strDate = SUBSTRING(@Photo,@Startposition,len(@Photo)-@Startposition)
if (len(@strDate ) < 24)
BEGIN
RETURN @getdate-2
END
if not (@strDate IS NULL)
BEGIN
SET @strDate = LEFT(@strDate ,patindex('%|%',@strPhotoDate )-1)
SET @strDate = replace(@strDate ,'th','')
SET @strDate = replace(@strDate ,'nd','')
SET @strDate = replace(@strDate ,'rd','')
SET @strDate = replace(@strDate ,'st','')
SET @strDate = replace(@strDate ,',','')
set @getdate-2 = cast(@strDate as DATETIME)
-- set @GetPhotoDate = convert(char,Convert(DateTime,@strPhotoDate,101))
-- set @GetPhotoDate = cast(CONVERT(datetime,@strPhotoDate)-convert(datetime,substring(@strPhotoDate,3,3)) as datetime)
--cast(@GetPhotoDate as DATETIME)
END
END
RETURN @GetPhotoDate
END
like that...
it will convert the 4/12/2008,4-july-2007....but not converting the 4th july 2008
22nd june 2008.
thanks in advance
April 17, 2008 at 11:57 am
Can you clean up the data in the table, or do you have to convert it on-the-fly in the proc/function?
If you can clean up the table, I recommend doing that.
There are only 31 ordinals you'll have to clean up, so:
update dbo.Table
set DateColumn = replace(DateColumn, '1st', '1') -- Also gets 21st, 31st
update dbo.Table
set DateColumn = replace(DateColumn, '2nd', '2') -- 2nd and 22nd
update dbo.Table
set DateColumn = replace(DateColumn, '3rd', '3') -- 3rd and 23rd
update dbo.Table
set DateColumn = replace(DateColumn, '4th', '4') -- 4th, 14th, 24th
... and so on
Then:
select distinct DateColumn
from dbo.Table
where isdate(DateColumn) = 0
That will give you your patterns of what needs to be fixed. It will probably end up being a very small number of combinations.
Once done, if you can convert the column to DateTime, instead of Char or Varchar, it will make things much better.
If you absolutely can't clean up the underlying data, just be sure your function doesn't have any false positives in it (things like removing the "st" from "August", when you mean to remove it from "1st").
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply