barkome
asked on
String to MMM YYYY
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
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
ASKER
Sure...
Not expecting any miracles but trying to see the feasibility with limited options
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
Not expecting any miracles but trying to see the feasibility with limited options
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.
... and even then it might not be wise to do so ...
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.
- 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.
ASKER
Sorry, I forgot to add, I'm only looking at converting the dates field to MMM YYYY
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 ??????????
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 ??????????
ASKER
its '02/29/2016' for an output of February 2016,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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))
ASKER
@ScottPletcher, is there a way to have this as a function?
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.
ASKER
Great, scalar will be okay, as the rows are not many in number, thanks.
CREATE FUNCTION dbo.Parse_Date (
@date_string varchar(100)
)
RETURNS varchar(30)
AS
BEGIN
RETURN (
SELECT SUBSTRING('__JanFebMarAprM ayJunJulAu gSepOctNov Dec', 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
@date_string varchar(100)
)
RETURNS varchar(30)
AS
BEGIN
RETURN (
SELECT SUBSTRING('__JanFebMarAprM
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]
) AS ca4
CROSS APPLY (
SELECT CASE WHEN date_year > 0 THEN NULL
ELSE '20' + SUBSTRING(date, LEN(date) - NULLIF(PATINDEX('%[0-9][0-
) AS ca5
)
END /*FUNCTION*/
GO
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.