ALI Imran
ALI Imran

Reputation: 1

How to i get Month & Year from SQL in Varchar Type

I Already Created Table (commercials) Column (MyDate) "Varchar(255)"

I Have some manual entries like:

dd-mm-yyyy
01-05-2019
02-05-2019
15-06-2019
16-06-2019

Now i want to select Only One Month and year. like

05-2019 or 06-2019

Please tell me how i can select only Month and year from this column.

Upvotes: 0

Views: 322

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You don't need any conversion among data types, since you want to extract a string from string, and to extract the part with mm-yyyy format pattern, just use substring() function as below :

select distinct substring(replace(MyDate,' ',''), 4, 7) as "Month-Year"
  from myTable;

Month-Year
----------
05-2019
06-2019

By the way, consider using replace(MyDate,' ','') (trim(MyDate) for version 2017+) against the probabilty of having space within the strings.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Using DISTINCT on Only MM-YYYY part of your data will return a MM-YYYY combination only once as per your requirement. Sample script is shown below-

SELECT DISTINCT RIGHT(MyDate, 7)
FROM commercials

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Use string operations:

select right(col, 4) as yyyy,
       right(col, 7) as mmyyyy,
       substring(col, 4, 2) as mm

Upvotes: 0

Related Questions