How to extract date fields from string/text field in sql server 2005

Question

There is a text filed in a table called as description. I would like to extract two date fields from this string when there is an occurrence of '~' character using sql server 2005 stored procedure. Help me out in this case.

Example: string: '长期租金;10/1/2012 ~ 10/31/2012'. At occurrence of ~ operator I would like to have from-date: 20121001 and to-date:20121031.

Answer 1

Here is a method which will give the start and end dates. I left most of the testing selects in place but commented out.

DECLARE @string AS NVARCHAR(255)
DECLARE @Seperator as char(1) = '~'
declare @CharStartDate as varchar(10)
declare @CharStopDate as varchar(10)
declare @StartDate as date
declare @StopDate as date

declare @I int

--SET @string = 'xvvvvvvcc;1/09/2012 ~ 1/10/2012xx'
--SET @string = 'xvvvvvvcc;12/31/2012 ~ 1/1/2012xx'
--SET @string = 'xvvvvvvcc;12/1/2012 ~ 10/0/2012xx'
SET @string = 'xvvvvvvcc;1/2/2012 ~ 1/3/2012xx'
--longest date 12/31/2011 = 10
--shortest date 1/1/2012 = 8
-- width of seperator = 3 

SELECT 
@CharStartDate = substring (@string, CHARINDEX(@Seperator,@string)-11,10)
,@CharStopDate = substring (@string, CHARINDEX(@Seperator,@string)+2,10)

--SELECT  @CharStartDate,@CharStopDate

select @I = ascii(substring(@CharStartDate,1,1))
While @I > 57
BEGIN
set @CharStartDate = substring(@CharStartDate,2,10)
--select @CharStartDate
select @I = ascii(substring(@CharStartDate,1,1))
END

select @I = ascii(substring(REVERSE(@CharStopDate),1,1))
While @I > 57
BEGIN
set @CharStopDate = REVERSE(substring(REVERSE(@CharStopDate),2,10))
--select @CharStopDate
select @I = ascii(substring(REVERSE(@CharStopDate),1,1))
END

--select ascii(';'),ascii('9'),ascii('8'),ascii('7'),ascii('6'),ascii('6'),ascii('4'),ascii('3'),ascii('2'),ascii('1'),ascii('0')
SELECT  @StartDate = @CharStartDate,@StopDate = @CharStopDate
--SELECT  @I,@string,@Seperator,@CharStartDate,@CharStopDate,@StartDate,@StopDate
select datediff(dd,@StartDate,@StopDate) AS 'DateDiff',@StartDate as 'Start Date',@StopDate as 'Stop Date'

I will leave it to you to check for the seperator.

Answer 2

In this instance you can use the following but really you need an exists clause or something like that to test the string for the tilde (~) and as everyone else has stated, this only works if the string always has a semicolon(;) and a tilde(~). You can convert to the strings into datetime fields if you need. I have placed the string in a variable to make it easier to read...

DECLARE @string AS NVARCHAR(255)

SET @string = '长期租金;10/1/2012 ~ 10/31/2012'

SELECT   StartDate = SUBSTRING(@string,CHARINDEX(';',@string)+1,LEN(@string)-CHARINDEX('~',@string)-1)
        ,EndDate =  LTRIM(RIGHT(@string,LEN(@string)-CHARINDEX('~',@string)))

Answer 3

i have never used the older version of SQL cause i just graduated but doesnt it have the EXTRACT() function?.. The syntax goes like this below.

SELECT First_Name ,    
  
    EXTRACT ( CAST(Created_date AS DATE) FROM Created_date ) AS Date_only ;

You specify 'First_name' to let SQL know you want it as a column and 'created_date' is the field from which youre trying to separate the date. the cast function converts your field to DATE value before extractig it.

i hope this helps . thank you. if im wrong please let me know i would like to improve myself.

Answer 4

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@Temp nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
  WHILE PatIndex ('%[^0-9~/]%', @Temp) > 0
  SET @Temp = Stuff(@Temp, PatIndex('%[^0-9~/]%', @Temp), 1, '')
  RETURN @Temp
END

DECLARE @string nvarchar(max) = '长期租金;10/1/2012 ~ 10/31/2012'
SELECT CONVERT(date, SUBSTRING([dbo].[RemoveAlphaCharacters](@string), 0,
       CHARINDEX('~', [dbo].[RemoveAlphaCharacters](@string))), 101) AS BDate,
       CONVERT(date, SUBSTRING([dbo].[RemoveAlphaCharacters](@string),
       CHARINDEX('~', [dbo].[RemoveAlphaCharacters](@string)) + 1,
       CHARINDEX('~', REVERSE([dbo].[RemoveAlphaCharacters](@string)))), 101) AS EDate