Reputation: 1
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
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
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
Reputation: 1269853
Use string operations:
select right(col, 4) as yyyy,
right(col, 7) as mmyyyy,
substring(col, 4, 2) as mm
Upvotes: 0