Solved

String to MMM YYYY

Posted on 2016-08-11
14
56 Views
Last Modified: 2016-08-15
Hello

I have this column called dates with string value, and I want to see how I can convert them to MMM YYYY, can anyone help?

DATES.JPG
0
Comment
Question by:barkome
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41753103
If you have a recent version of MS SQL you could use TRY_CAST() like this

select try_cast('Apr-16' as date)

it returns NULL if it cannot complete the conversion (e.g. it returns NULL from that example)

But I suspect what you want will simply not be easy.
0
 

Author Comment

by:barkome
ID: 41753113
This was the output...

DATES_1.JPG
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41753121
why not provide a set of data from that column we can use? (sample data, not an image)
and, also what value you want from each (the expected result)

e.g. what do you want from "February"?

but you do realize miracles don't occur in SQL I trust
0
 

Author Comment

by:barkome
ID: 41753128
Sure...

IF OBJECT_ID('tempdb..#TEMP_DATES') IS NOT NULL   DROP TABLE #TEMP_DATES;
CREATE TABLE #TEMP_DATES (DATES VARCHAR(100)) 
INSERT INTO #TEMP_DATES  (DATES)
VALUES ('July 7, 2016'), ('April-16'),('June 2016'),('Febuary'),('Apr-16'),('4/30/2016'),('02/29/2016'),('16-Apr')

SELECT * FROM #TEMP_DATES

Open in new window


Not expecting any miracles but trying to see the feasibility with limited options
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41753134
You're going to have to write a series of TRY_CAST and UPDATE statements to test each acceptable format to pull this off, including a column like 'FormatID' to identify each value and the format that successfully converted it to a date, and make assumptions.
  • Does 'February' mean the first day of February in the current year, or not a date?
  • Does 'April-16' mean 4/1/2016, or some other day, or not a date?

... and even then it might not be wise to do so ...
  • Does 16-Apr mean 4/16/current year, or 4/?/2016

Looking at the image I'm guessing that the source of data is Excel.  A far better idea would be to contact the source of this data and have them export the file as a .csv with the date values in a standard format.  Excel is great for editing stuff, but in your case Excel is terrible for editing stuff as any little change will cause an ETL process to fail as there is a 'contract' between source and destination, and Excel allows for too many ways to break the contract.
0
 

Author Comment

by:barkome
ID: 41753137
Sorry, I forgot to add, I'm only looking at converting the dates field to MMM YYYY
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41753149
What year is to be assumed when one isn't provided?

e.g. the current year? or some other column?

this is why I asked for a column of the expected result e.g.

input_col   output_col
Februaury ??????????
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:barkome
ID: 41753154
its '02/29/2016' for an output of February 2016,
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41753192
I'd parse out what you can while leaving the original value as a string,  until you determine the month and year, if available/determinable.  I use CROSS APPLYs to move the computations out of the main SELECT and to allow subsequent computations to use previous ones without having to recompute them.
For example:


SELECT dates,
    SUBSTRING('__JanFebMarAprMayJunJulAugSepOctNovDec', ISNULL(dates_month, dates_month2) * 3, 3) AS month,
    COALESCE(dates_year, dates_year2, YEAR(GETDATE())) AS year
FROM #TEMP_DATES d
CROSS APPLY (
    SELECT CASE
        WHEN dates LIKE '%Jan%' THEN 1
        WHEN dates LIKE '%Feb%' THEN 2
        WHEN dates LIKE '%Mar%' THEN 3
        WHEN dates LIKE '%Apr%' THEN 4
        WHEN dates LIKE '%May%' THEN 5
        WHEN dates LIKE '%Jun%' THEN 6
        WHEN dates LIKE '%Jul%' THEN 7
        WHEN dates LIKE '%Aug%' THEN 8
        WHEN dates LIKE '%Sep%' THEN 9
        WHEN dates LIKE '%Oct%' THEN 10
        WHEN dates LIKE '%Nov%' THEN 11
        WHEN dates LIKE '%Dec%' THEN 12
        ELSE NULL END AS dates_month
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN dates_month > 0 THEN NULL ELSE REPLACE(LEFT(dates, 2), '/', '') END AS dates_month_string
) AS ca2
CROSS APPLY (
    SELECT CASE
        WHEN dates_month_string LIKE '%[^0-9]%' THEN NULL
        WHEN dates_month_string < 1 OR dates_month_string > 12 THEN NULL
        ELSE dates_month_string END AS dates_month2
) AS ca3
CROSS APPLY (
    SELECT SUBSTRING(dates, NULLIF(PATINDEX('%[2][012][0-9][0-9]%', dates), 0), 4) AS dates_year
) AS ca4
CROSS APPLY (
    SELECT CASE WHEN dates_year > 0 THEN NULL
        ELSE '20' + SUBSTRING(dates, LEN(dates) - NULLIF(PATINDEX('%[0-9][0-9]%', REVERSE(dates)), 0), 2) END AS dates_year2
) AS ca5
1
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41753336
select right(convert(varchar, getdate(), 103), 7)
or
SELECT DATENAME(MONTH, @thedate) + '/' + DATENAME(YEAR, @thedate)
or
Select SubString(Convert(Varchar(Max), GetDate(),0), 1, 3) + '/' + Cast(Year(GetDate()) As Varchar(Max))
0
 

Author Comment

by:barkome
ID: 41754469
@ScottPletcher, is there a way to have this as a function?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41754857
Sure.  Do you want a scalar function or an inline table-valued functon?  The itvf will perform better if you do this across a lot of rows.
0
 

Author Comment

by:barkome
ID: 41754862
Great, scalar will be okay, as the rows are not many in number, thanks.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41756460
CREATE FUNCTION dbo.Parse_Date (
    @date_string varchar(100)
)
RETURNS varchar(30)
AS
BEGIN
RETURN (
    SELECT SUBSTRING('__JanFebMarAprMayJunJulAugSepOctNovDec', ISNULL(date_month, date_month2) * 3, 3) + ' ' +
        CAST(COALESCE(date_year, date_year2, YEAR(GETDATE())) AS varchar(4)) AS date_mmm_yyyy
    FROM (
        SELECT @date_string AS date
    ) AS input_param    
    CROSS APPLY (
        SELECT CASE
            WHEN date LIKE '%Jan%' THEN 1
            WHEN date LIKE '%Feb%' THEN 2
            WHEN date LIKE '%Mar%' THEN 3
            WHEN date LIKE '%Apr%' THEN 4
            WHEN date LIKE '%May%' THEN 5
            WHEN date LIKE '%Jun%' THEN 6
            WHEN date LIKE '%Jul%' THEN 7
            WHEN date LIKE '%Aug%' THEN 8
            WHEN date LIKE '%Sep%' THEN 9
            WHEN date LIKE '%Oct%' THEN 10
            WHEN date LIKE '%Nov%' THEN 11
            WHEN date LIKE '%Dec%' THEN 12
            ELSE NULL END AS date_month
    ) AS ca1
    CROSS APPLY (
        SELECT CASE WHEN date_month > 0 THEN NULL ELSE REPLACE(LEFT(date, 2), '/', '') END AS date_month_string
    ) AS ca2
    CROSS APPLY (
        SELECT CASE
            WHEN date_month_string LIKE '%[^0-9]%' THEN NULL
            WHEN date_month_string < 1 OR date_month_string > 12 THEN NULL
            ELSE date_month_string END AS date_month2
    ) AS ca3
    CROSS APPLY (
        SELECT SUBSTRING(date, NULLIF(PATINDEX('%[2][012][0-9][0-9]%', date), 0), 4) AS date_year
    ) AS ca4
    CROSS APPLY (
        SELECT CASE WHEN date_year > 0 THEN NULL
            ELSE '20' + SUBSTRING(date, LEN(date) - NULLIF(PATINDEX('%[0-9][0-9]%', REVERSE(date)), 0), 2) END AS date_year2
    ) AS ca5
)
END /*FUNCTION*/
GO
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now