CODEWITHSUNDEEP
sqlsql-serverdatabase
suraj08
suraj08

Reputation: 119

Different date formats in single column in sql server

I am using SQL server. We have date column in table but some of them are stored in different format.

For e.g: We have records with format 2015-12-09 00:00:12.000 but some records are there with format as 2015/09/09 00:08:09.000 or any other valid date type. How can I identify records with different date format from table. I tried using isdate() function but as all date are valid there is no luck. Can you please guide me with this.

Upvotes: 1

Views: 3856

Answers (2)

WonderWorker
WonderWorker

Reputation: 9062

Solution part 1

Although it is possible that this solution might not completely solve your issue, if at least you can unify your dates to look similar to each other then your position should be very much improved by placing the following at the top of your query:

SET DATEFORMAT DMY

OR 

SET DATEFORMAT YMD

OR 

SET DATEFORMAT MDY

Example

SELECT
    [date]
FROM
    [your_table]

Points of note:

  • M means month, D means day and Y means year.

  • Setting the DATEFORMAT affects both how dates appear in the result set and how dates are converted to VARCHAR and similar.

  • If you don't set the DATEFORMAT then running the same stored procedure on different machines/set-ups can yield differing results.

Solution part 2

You can also perform some string manipulation to replace -, /, etc. with the character of your choice.

..Put the following at the top of your query:

DECLARE @DateSeparator NVARCHAR(1) = '/'

..Use the following as part of your select statement: 

REPLACE(REPLACE([date], '/', @DateSeparator), '-', @DateSeparator)

Example

SELECT
    REPLACE(REPLACE(CONVERT(NVARCHAR(20), [date]), '/', @DateSeparator), '-', @DateSeparator) AS [date]
FROM
    [your_table]

Points of note:

  • In the above example, the date separator is set to /, but change the value of @DateSeparator to the date separator of your choice.

Both solutions combined

Example

SET DATEFORMAT YMD

SELECT
    REPLACE(REPLACE(CONVERT(NVARCHAR(20), [date]), '/', @DateSeparator), '-', @DateSeparator) AS [date]
FROM
    [your_table]

Points of note:

  • Replace [your_table] with the name of your source table.

Upvotes: 1

Tom H
Tom H

Reputation: 47464

Since the dates were stored as VARCHAR instead of DATETIME (as they should have been) then there really is no way through SQL to determine it. Is the date '2016-02-01' February 1st or January 2nd? If you can't tell then how is any computer code going to figure it out?

Your best bet is to go back through the application(s) that have inserted or updated data in the table and try to figure out what they might have used. If users were just typing in data then that's unlikely to help, although maybe you can look for some consistencies - Janice always enters data MM/DD/YYYY, but Pierre always puts it in as DD/MM/YYYY, etc. That's probably your best bet in narrowing it down. Otherwise, you might look at other data in your system - if the table also has an inserted_on column for example, then maybe that and your other date would usually be within a few days of each other, etc.

Upvotes: 0

Related Questions