CODEWITHSUNDEEP
sqlsql-servert-sqlsubstring
salar
salar

Reputation: 157

Substring and replace together?

I am new to T-SQL. I want to use substring and replace together, but it doesn't work as expected, or maybe there is some extra function that I do not know.

I have a column that stores date time, and the format is like this : '1393/03/03'.

But I want to show it like this: '930303' , i.e. I want these characters '13' and '/' to be omitted. I tried substring and replace but it does not work.

Here is my code : 

SELECT SUBSTRING(CreateDate,2,REPLACE(CreateDate,'/',''),8)

Can you help me ?

Upvotes: 0

Views: 10562

Answers (6)

Jesuraja
Jesuraja

Reputation: 3844

Try RIGHT function

SELECT RIGHT(REPLACE(CreateDate,'/',''),6)

Upvotes: 0

user3884116
user3884116

Reputation: 9

DECLARE @STR VARCHAR(50) = '1393/03/03'

SELECT SUBSTRING(REPLACE(@STR,'/',''),3,LEN(@STR))enter code here

Upvotes: 0

RdPC
RdPC

Reputation: 689

You could use the CONVERT function.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Allows converting dates from one format to another, in this case, 111- JAPAN yyyy/mm/dd to 12 - ISO yymmdd

SELECT convert(NVARCHAR(50), GETDATE(), 12)

SELECT convert(NVARCHAR(50), CAST('1393/03/03' AS DATE), 12)

Upvotes: 1

Pred
Pred

Reputation: 9042

If your input column's data type is DATETIME or DATETIME2 (or any DATE/TIME data types), then you should convert it to VARCHAR, then replace the / character. (DATETIME format styles)

If it is a string already (VARCHAR), then the below solution will do the trick.

DECLARE @X DATETIME2 = '1393/03/03' -- Example for DATE/TIME data type
DECLARE @Y VARCHAR(32) = '1393/03/03' -- Example for VARCHAR data type

SELECT
    @X
    , @Y
    , REPLACE(CONVERT(VARCHAR(32), @X, 11), '/', '') -- Japan style
    , CONVERT(VARCHAR(32), @X, 12) -- ISO style
    , REPLACE(STUFF(@Y, 1, 2, ''), '/', '')

EDIT

Please note that formatting is not for the database engine, do the formatting in the frontend application if possible.

EDIT 2 Your error is: SELECT SUBSTRING(CreateDate,2,REPLACE(CreateDate,'/',''),8)

SUBSTRING has 3 arguments:

  • expression (character, binary, text, ntext, or image)
  • start (bigint)
  • length (bigint)

In your query there are 4 arguments and the third is a VARCHAR type. With your solution, the valid expressoin is: SUBSTRING(REPLACE(CreateDate, '/', ''), 3, 6) (if the CreateDate is VARCHAR or NVARCHAR. If it is DATETIME or DATETIME2, you should use the methods shown above.)

Upvotes: 0

Thanos Markou
Thanos Markou

Reputation: 2623

Try this:

SELECT REPLACE(SUBSTRING(CreateDate,2,8),'/','')

Upvotes: 1

Robert
Robert

Reputation: 25753

Try this way to test my solution:

SELECT SUBSTRING(REPLACE('1393/03/03','/',null),3,8)

and with your column it will be:

SELECT SUBSTRING(REPLACE(CreateDate,'/',null),3,8)

Upvotes: 0

Related Questions