How to convert string to date in SQL for all the items in the same column?-CodePudding

Currently, one of the column in the SQL table contains date in DMMYYYY format and the column dtype is VARCHAR. But I would like to convert it into in date dtype format i.e. DD/MM/YYYY format

Current format:

8012005
9012005
10012005

Required format:

08/01/2005
09/01/2005
10/01/2005

My Attempt

SELECT [serial]
  ,[srn]
  ,[ticker]
  ,[series]
  ,[QT]
  ,[DQ]
  ,[DQ/QT]
  ,[TIMESTAMP]
  FROM [Indexer].[dbo].[EquityNSEDAT]

SELECT CONVERT(TIMESTAMP, '%D/%m/%y');

CodePudding user response：

The real problem is your table here, you need to fix your design. To do that you need to change the data type of your column from a (presumably) string based data type to a date data type. I also assume you have bad data, and so retain that bad data in a new column. You'll need to review that data after and fix it manually.

Firstly, fixing your design. This changes your string from am ambiguous value to the an ISO format (yyyyMMdd), then changes the data type of your columns:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (YourDate varchar(8) NULL);

INSERT INTO dbo.YourTable
VALUES ('8012005'),
       ('9012005'),
       ('10012005'),
       ('31112005'),
       ('4132005');
GO

ALTER TABLE dbo.YourTable ADD BadDate varchar(8) NULL;
GO

UPDATE YT
SET BadDate = CASE WHEN TRY_CONVERT(date,FD.FormattedDate) IS NULL THEN YT.YourDate END,
    YourDate = CONVERT(varchar(8),TRY_CONVERT(date,FD.FormattedDate,112),112)
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(RIGHT('00000000'   YT.YourDate,8)))PD(PaddedDate)
     CROSS APPLY (VALUES(CONCAT(RIGHT(PD.PaddedDate,4), SUBSTRING(PD.PaddedDate,3,2),LEFT(PD.PaddedDate,2))))FD(FormattedDate);
GO
ALTER TABLE dbo.YourTable ALTER COLUMN YourDate date NULL;
GO
SELECT *
FROM dbo.YourTable;
GO
--Get bad data
SELECT BadDate
FROM dbo.YourTable
WHERE BadDate IS NOT NULL;

GO
DROP TABLE dbo.YourTable;

After you've done that, to get the value displayed as you want, configure this in your presentation layer, not the SQL layer; leave the data type as a date in your SQL layer.

CodePudding user response：

To convert a date in DMMYYYY format (e.g. "8012005") to DD/MM/YYYY format (e.g. "08/01/2005") in a SQL query, use the CONVERT() function with the appropriate style parameter.

In your case, the query below converts the TIMESTAMP column from DMMYYYY format to DD/MM/YYYY format:

SELECT [serial]
  ,[srn]
  ,[ticker]
  ,[series]
  ,[QT]
  ,[DQ]
  ,[DQ/QT]
  ,CONVERT(DATE, TIMESTAMP, 105) AS TIMESTAMP
FROM [Indexer].[dbo].[EquityNSEDAT]