CODEWITHSUNDEEP
sqlsql-servert-sqlssrs-2012
stlblues123
stlblues123

Reputation: 25

Need to format date in SQL from string using charindex

I am trying to format a date in mm/dd/yyyy from string data using charindex, the date in the string is yyyymmdd. I have tried using convert, cast, castconvert.

If anyone could help that would be great! I have sample code

CONVERT (
                       VARCHAR (10)
                     , CASE
                           WHEN CHARINDEX ('^3507=', reg.BetaRequestString) = 0 THEN
                               ''
                           ELSE
                               RIGHT(LEFT(reg.BetaRequestString, CHARINDEX ('^3507=', reg.BetaRequestString) + 13), 8)
                       END
                     , 101

Upvotes: 0

Views: 1259

Answers (4)

Egor Mikhailov
Egor Mikhailov

Reputation: 29

You can also use datetime style in convert function: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles

DECLARE @yyyymmdd sysname = '20180101'
SELECT CONVERT(sysname, CONVERT(datetime, @yyyymmdd, 112), 101)

Upvotes: 0

Hasan Jafarov
Hasan Jafarov

Reputation: 628

declare @a nvarchar(10)
set @a='20180203'
select 
convert(nvarchar(10),substring(@a,5,2)+'/'+substring(@a,7,2)+'/'
+substring(@a,1,4),101)

Upvotes: 1

Raj
Raj

Reputation: 502

When you know your input date format , you can split the parts and reorganise the date.

declare @date varchar(100)='20021230' , 
 @Year int,@month int ,@day int

select @year=Substring(@date,1,4) ,@month=SUBSTRING(@date,5,2),@day=SUBSTRING(@date,7,2)

select convert(VARCHAR(10),(DATEFROMPARTS(@year,@month,@day)),101)

output-12/30/2002

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

It appears you want this:

CASE WHEN CHARINDEX ('^3507=', reg.BetaRequestString) = 0 
     THEN ''
     ELSE CONVERT(VARCHAR(10),RIGHT(LEFT(reg.BetaRequestString, CHARINDEX ('^3507=', reg.BetaRequestString) + 13), 8), 101)
END

Upvotes: 0

Related Questions