Sample Date Different format SQL Server

Question

Sample Date Different format SQL Server

Navigation

#1 by (2 votes)

0

Hello I have a Table that has a Date column, this has three date formats (04/10/2018) (2018-10-04) (20181004), what I would like to do is in a query to be able to match the format of three. Thanks

sql-server-2012

asked by Manuel Valdivieso 04.10.2018 в 15:51

source

1 answer

Make a query with LINQ to entities PHP environment variable of xampp is not added to the path

score 2 · Accepted Answer

First of all a clarification for others who read this, what you have is a chain that represents a date, the dates themselves have no format. In this chain so you say you have 3 different formats

04/10/2018
2018-10-04
20181004

Which match three different formats that the CONVERT() the only thing that would correspond is to verify in advance which of them it is, seeing if it has scripts, bars or nothing:

SELECT  F.FechaString,
    CASE WHEN CHARINDEX('/', F.FechaString) > 0 THEN CONVERT(DATETIME, F.FechaString, 103) -- British
         WHEN CHARINDEX('-', F.FechaString) > 0 THEN CONVERT(DATETIME, F.FechaString, 111) -- Japan
         ELSE CONVERT(DATETIME, F.FechaString, 112)                                        -- ISO
    END
    FROM (  SELECT '04/10/2018' AS FechaString UNION
            SELECT '2018-10-04' UNION
            SELECT '20181004'
    ) F

Exit:

╔═════════════╦══════════════════════════╗
║ FechaString ║                          ║
╠═════════════╬══════════════════════════╣
║ 04/10/2018  ║ 04/10/2018 12:00:00 a.m. ║
╠═════════════╬══════════════════════════╣
║ 2018-10-04  ║ 04/10/2018 12:00:00 a.m. ║
╠═════════════╬══════════════════════════╣
║ 20181004    ║ 04/10/2018 12:00:00 a.m. ║
╚═════════════╩══════════════════════════╝

If you had SQL 2012 or higher, it may be convenient to replace CONVERT() by TRY_CONVERT() , the first will generate an exception / error if it fails to convert the string to the date, the second does not (but returns NULL), so that could help you if you eventually have more formats than you mentioned.