Convert nvarchar date (DD/MM/YYYY) to Date Period (YYYY_MM)

sql convert string to date dd/mm/yyyy
conversion failed when converting date and/or time from character string.
sql convert string to date yyyymmdd
convert datetime to date sql
convert(varchar to date in sql)
sql date format dd-mmm-yyyy
sql convert string to date yyyymmddhhmmss
sql insert date format yyyy-mm-dd

I am trying to convert this into a period format, so e.g. 2018_05 (YYYY_MM). currently the data is in DD/MM/YYYY format.

I tried a cast code but it returns me YYYY_DD.

SELECT   
CASE WHEN RESERVED_FIELD_4 IS NULL THEN NULL
     ELSE cast(year(RESERVED_FIELD_4) as Nvarchar (4)) 
     +'_'+right('00'+cast(month(RESERVED_FIELD_4)   as Nvarchar (2)),2)
END AS [DATAFEED_PERIOD]

I expect/want to see YYYY_MM.

Assuming RESERVED_FIELD_4 is a string type (char/nchar/varchar/nvarchar) the simplest solution would be to use substring:

CASE
WHEN RESERVED_FIELD_4 IS NULL THEN NULL
ELSE SUBSTRING(RESERVED_FIELD_4, 7, 4) + '_'+ SUBSTRING(RESERVED_FIELD_4, 4, 2)
END AS [DATAFEED_PERIOD]

If it's a date/datetime/datetime2 data type, the simplest solution would be to use format:

FORMAT(RESERVED_FIELD_4, 'yyyy_MM')

But for better performance you can use convert and stuff:

SELECT STUFF(CONVERT(char(6), RESERVED_FIELD_4, 112), 5, 0, '_')

In case your format is actually d/m/y the simplest option is to convert to date and than back to string:

SELECT STUFF(CONVERT(char(6), CONVERT(Date, RESERVED_FIELD_4, 103), 112), 5, 0, '_')

Date and Time Conversions Using SQL Server, To change the format of the date, you convert the requested date to a string and function; To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23) 130, select convert(nvarchar, getdate(), 130), date output. Since they are nvarchar it allows for anything to be entered into it, i.e. DD/MM/YYYY, MM/DD/YYYY, YYYYMMDD, and 'banana', which could cause such a conversion to fail. Aside from that, SQL Server Date Styles (formats) using CONVERT() is the definitive cheat sheet for T-SQL date conversions around here.

This is the common problem of storing a date with a VARCHAR column. You are guessing that the stored pattern is DD/MM/YYYY but the SQL engine doesn't know that and is currently assuming the MM/DD/YYYY pattern.

Please check these results:

-- MM/DD/YYYY
SELECT
    DAY  ('05/01/2019'),        -- 1
    MONTH('05/01/2019')         -- 5

-- DD/MM/YYYY
SELECT
    DAY  ('25/05/2019'),        -- Conversion failed when converting date and/or time from character string
    MONTH('25/05/2019')         -- Conversion failed when converting date and/or time from character string.

To display what you want correctly use string functions:

SELECT
    RIGHT(RESERVED_FIELD_4, 4) + '_' + SUBSTRING(RESERVED_FIELD_4, 4, 2)

But you should actually fix the values on your VARCHAR column, cast them to DATE and store the values as DATE.

ALTER TABLE YourTable ADD ReservedField4Date DATE

UPDATE YourTable SET 
    ReservedField4Date = CONVERT(DATE, 
        RIGHT(RESERVED_FIELD_4, 4)                      -- Year
        + '-' + SUBSTRING(RESERVED_FIELD_4, 4, 2)       -- Month
        + '-' + LEFT(RESERVED_FIELD_4, 2))              -- Day

ALTER TABLE YourTable DROP COLUMN RESERVED_FIELD_4

EXEC sp_rename 'SchemaName.YourTable.ReservedField4Date', 'RESERVED_FIELD_4', 'COLUMN'

Beware that changing the column type might affect other queries that assume this is a VARCHAR column.

SQL Datetime Conversion - String Date Convert Formats, SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime  hi. i have a column called ddate which is nvarchar (255) and contains values in the following format ''01 Sep 2011'. I have created a new column called ddate2 where i want to convert the values

If your data is in DD/MM/YYYY format, then it is being stored as a string. Hence, string functions come to mind:

select right(RESERVED_FIELD_4) + '_' + substrint(RESERVED_FIELD_4, 4, 2)

Convert Datetime to String in a Specified Format in SQL Server, 20 or 120, ODBC canonical, yyyy-mm-dd hh:mi:ss(24h). –, 21 or 121, ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset​  I am trying to convert this into a period format, so e.g. 2018_05 (YYYY_MM). currently the data is in DD/MM/YYYY format. I tried a cast code but it returns me YYYY_DD. SELECT CASE WHEN

In SQL-SERVER you can use 'format'

format(dy,@your_date) as day_of_year

month(@your_date) as month

Try this:

Select concat(month(@your_date),'_'year(@your_date)) as your_period

this is a reference

SQL Date Utility · GitHub, MSSQL getdate returns current system date and time in standard internal format SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02 ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL. GO. Date string (VARCHAR) or literal to convert. format. Date pattern of string identical to the one used by the Java™ SimpleDateFormat class, and is described in Date-time formatting. Omit this option to convert by using the default format, which is "yyyy-MM-dd HH:mm:ss.SSSSSSSSS".

Why not just do conversations ? :

SELECT REPLACE(CONVERT(VARCHAR(7), CONVERT(date, RESERVED_FIELD_4, 101), 102), '.', '_')

This assumes RESERVED_FIELD_4 is date type.

SQL convert date, SQL convert date: Date time results after convert sql date How to convert from varchar, nvarchar, char, nchar to sql date using CAST hh:mm:ss[.nnn]; Smalldatetime returns date with this format: YYYY-MM-DD hh:mm:ss  Hi all, I am working with a Oracle 9i R2 database SQL>select TO_CHAR(sysdate, 'MM/DD/YYYY') CHRDATE from dual; CHRDATE-----11/14/2005 SQL>select TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') mydate from dual; MYDATE-----14-NOV-05 I want to retain the 4 digit year.

SQL Server CONVERT() Function, The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary). expression The format used to convert between data types, such as a date or string format. Can be one of 20, 120, yyyy-mm-dd hh:mi:ss, ODBC canonical (24 hour clock) mmmZ, ISO8601 (with time zone Z). format (longdate ("START_DATE"), 'YYYY-MM-DD') It will convert your NVARCHAR Data Type to DATE Data Type. Data Preview Result, Refer this link to get calculated column DATE functions. Date Functions - Creating a Graphical Calculation View and Previewing its Output - SAP Library. Regards, Muthuram

COnvert Date time formate yyyy-mm-dd to MM/yyyy formate, Hi i want convert date in Convert Date time formate yyyy-mm-dd to MM/yyyy formate anu body can send code. Suppose you have data in a table in format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD We do face many such scenarios when we do not have date format as per our requirement. We cannot change table properties to satisfy each requirement.

SQL DATE Format, 21 or 121, yyyy-mm-dd hh:mi:ss.mmm(24h), ODBC canonical with milliseconds. This is the Default for time, date, datetime2, and datetimeoffset. You also can use a formula to convert the date forma mm/dd/yyyy to a string yyyymmdd as text. Select a blank cell that you want to place the converted date string, and type this formula =TEXT(A1,"yyyymmdd"), drag auto fill handle down to apply this formula to cells.

Comments
  • Code seems OK, can you share an example? The problem might be RESERVED_FIELD_4 data type.
  • String/date functions are highly vendor-specific. It's going to be very hard to answer this question without knowing what rdbms you are working with.
  • The result at the moment is that oneDATAFEED_PERIOD 2017_01 RESERVED_FIELD_4 01/02/2017 But it should return 2017_02 in datafeedperiod, as February is the correct month.
  • Also, what's the data type of RESERVED_FIELD_4?
  • Which dbms are you using? (That query is product specific.)
  • THIS one solved it, thanks you so much ! CASE WHEN RESERVED_FIELD_4 IS NULL THEN NULL ELSE SUBSTRING(RESERVED_FIELD_4, 7, 4) + '_'+ SUBSTRING(RESERVED_FIELD_4, 4, 2) END AS [DATAFEED_PERIOD]
  • only problem with this code is that if the reserved field 4 does not have 00 (2) digits format, then it doesnt work correctly. how can i modify that in case it is 7/2/2018 that it is still converted to 2018_02?
  • CASE WHEN RESERVED_FIELD_4 IS NULL THEN NULL ELSE SUBSTRING(RESERVED_FIELD_4, 7, 4) + '_'+ SUBSTRING(RESERVED_FIELD_4, 4, 2) END AS [DATAFEED_PERIOD] this code is working fine now, but not if the string is d/m/yyyy, how can i change it so it adds a leading zero in case it is not in dd/mm/yyyy format?
  • reserved field 4 is nvarchar, so that one doesnt work
  • @Robert. . . then convert it to date first.