-2

I have a table with a column varchar(2048) and inside I have data in date format.

But my data is in the format "20/12/2021" (British standard) and also "12/20/2021" (US standard).

Can I convert the varchar column in a date column with both standards?

Thom A
  • 88,727
  • 11
  • 45
  • 75
AGTP
  • 1
  • 3
    The best solution is to add a column of type DateTime2 and drop the date part in this column. This problem you have now is exact the reason why you should always use the correct datatype – GuidoG Jun 28 '22 at 09:04
  • 4
    To answer your question, unless you have some means of knowing which format each date should be, it will be impossible – GuidoG Jun 28 '22 at 09:05
  • 4
    As the column is defined as a `varchar(2048)` *presumably* it contains more than just 10 character values representative of ambiguous dates; otherwise it wouldn't be defined as a needing to be 2048 characters long. As for your data, nope, the integrity of the data is lost; you effectively have no idea what date it is unless you have another column that denotes what culture the date is. – Thom A Jun 28 '22 at 09:05
  • @GuidoG and how i convert the data to this column? – AGTP Jun 28 '22 at 09:07
  • 5
    You cannot anymore, hence you should have never stored it like that. I sympathize with you but I am afraid there is no solution – GuidoG Jun 28 '22 at 09:08
  • @Larnu its always 10 characters – AGTP Jun 28 '22 at 09:10
  • 4
    Then why is it a defined as needing to be *up to* 2048 characters long and not 10? What do you need the other 2038 characters for? – Thom A Jun 28 '22 at 09:11
  • I dont know, i just saw this table yesterday. – AGTP Jun 28 '22 at 09:13
  • You tell us, since you are using an ambiguous format, how would know whether it's a UK or US date? – Dale K Jun 28 '22 at 09:25
  • Since 3 june 2022, because happen a change in the files from the client. – AGTP Jun 28 '22 at 09:34
  • 1
    welcome to stackoverflow agtp. please take a [tour] and learn [ask]. as the others have pointed out, what you asked is not possible in any trivial steps. is this a migration from legacy software? you will need at least resolve any ambiguous dates - e.g. 04/08/2000 is it August 4th or April 8th? – Bagus Tesa Jun 28 '22 at 09:35
  • But since the 3rd June **is** an ambiguous date, unles you also store the date you loaded the data as a **proper** date you still cannot tell which is which. – Dale K Jun 28 '22 at 09:37
  • until 3 june 2022 it's (04/08/2000 - August 4th). Since 3 june 2022 it's April 8th – AGTP Jun 28 '22 at 09:39
  • I store the insert_date, a datetime column – AGTP Jun 28 '22 at 09:40
  • @agtp just an idea, you can create a `datetime` column then you run two different query, one for data pre 3 june 2022 and the other for after. you can check ["Sql Server string to date conversion" QA](https://stackoverflow.com/a/7183924) for many ways to parse date in [tag:sql-server]. give it a shot and good luck. – Bagus Tesa Jun 28 '22 at 09:44
  • This would probably help. https://stackoverflow.com/questions/72784116/i-am-getting-an-error-when-converting-a-varchar-column-into-a-data-column – JonTout Jun 28 '22 at 10:14
  • @BagusTesa i think it worked. until 3 june 2022 i use convert(date, [Date], 103) and after convert(date, [Date], 101). Now i have a new column date type, all with format 2022-06-28. Thanks – AGTP Jun 28 '22 at 12:44
  • If you have another column in the table that can be used to identify which date format is being used, it's basically impossible. A) 20/12/2021 is easy to figure out. B) What is 08/01/2022? Without that additional bit of information, you might be able to convert some data as in case (A), but (B) will need to be manually converted (which will suck if you have 100s or more rows of data). – horace Jun 28 '22 at 13:47

1 Answers1

0

This is a data/business problem and so will need further decisions by someone who knows the data/business.

As you do not know what format this data is in, you can work out some, but not all - for example:

SELECT 
    *,
    SUBSTRING(D.Date, 0, CHARINDEX('/', D.Date)),
    SUBSTRING(D.Date, CHARINDEX('/', D.Date) + 1, CHARINDEX('/', D.Date, CHARINDEX('/', D.Date)) - 1),

    CASE 
        WHEN SUBSTRING(D.Date, 0, CHARINDEX('/', D.Date)) > 12 THEN 'UK Date'
        WHEN SUBSTRING(D.Date, CHARINDEX('/', D.Date) + 1, CHARINDEX('/', D.Date, CHARINDEX('/', D.Date)) - 1) > 12 THEN 'US Date'
        ELSE 'Indeterminate Date'
    END
FROM 
    ( VALUES 
        ('30/05/2020'),
        ('10/25/2020'),
        ('08/06/2020')
    ) AS D (Date);

You would need to prepare a list of the indeterminate dates and use other information from your data to work out which is which. For example, if these are order dates and there are also shipping dates you could work out because you expect the dates to be close:

Order Date: 02/06/2020 Shipping Date: 02/09/2020

Therefore (unless your company takes 3 months to despatch), this is an American date.

Jonathan Twite
  • 932
  • 10
  • 24