Replace the MM/DD/YYYY dates with YYYY-MM-DD

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replace the MM/DD/YYYY dates with YYYY-MM-DD

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-09-23 : 05:59:07

Hi,
In One of the tables in the database the dates are somehow stored in different formats, i want to search for the dates which are in the format MM/DD/YYYY and update them to the format YYYY-MM-DD

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-23 : 06:32:50

No, no no.... dates are not stored in format if stored as a date or datetime type datatype. Dates should not be stored as strings. You can format on retrieval of data in the front end or by using the convert function.

If you'd like SSMS to show dates in a certain format by default when you run a query I think this can be determined by what localisation the database is set, if that's the right word. E.g. if it's set to UK, then you'll get the dataes DD/MM/YYYY, if USA then MM/DD/YYYY.

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-09-23 : 06:40:14

Now the problem is that in my tables the dates are varchar and i cannot change that....so is there a way that i can check for the MM/DD/YYYY strings and update them to YYYY-MM-DD

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2010-09-23 : 06:47:26

Hi,

try this below

SELECT convert(datetime, <date string>,111) -- yyyy/mm/dd

or

SELECT REPLACE(CONVERT(datetime, <date string>, 111),'/','-') -- yyyy-mm-dd

Hope this is useful

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 12:24:05

quote:
Originally posted by nitsmooth

Now the problem is that in my tables the dates are varchar and i cannot change that....so is there a way that i can check for the MM/DD/YYYY strings and update them to YYYY-MM-DD

are you sure there's no other formats in which date is stored in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-09-24 : 02:22:35

The "EntryDate" field in the table is varchar and the dates have got stored in two formats

1) YYYY-MM-DD
2) MM/DD/YYYY (I want to change this one to YYYY-MM-DD) ...U see??

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 03:50:19

Change them both to DATETYIME datatype. Much more useful and flexible, and data will be validated to be a valid date

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-09-24 : 05:01:03

If you mean to change the dataType of the entryDate field of the table, i cannot do that???

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-24 : 05:03:35

why can't you do that?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 05:08:33

You should do that. Storing dates in VARCHAR is a bad idea for all sorts of reasons. Look at the current muddle you have - dates in two different formats. Have you also got some invalid dates in there? 31-Feb - that type of thing?

An option would be to add a new column to store the entryDate in a DATETYPE datatype, and leave the original Varchar column there. Or set up a VIEW that has the entryDate in DATETIME datatype based on whatever parsing functions you want:


CREATE VIEW MyView
AS
SELECT [V_ID] = ID,
       [V_entryDate] = CASE WHEN entryDate LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' THEN CONVERT(datetime, REPLACE(entryDate, '-', ''))
                            WHEN entryDate LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, entryDate, 111)
                            ELSE NULL
                            END
FROM MyTable

Then check for goofy dates with


SELECT *
FROM MyTable
    JOIN MyView
        ON V_ID = ID
WHERE     entryDate IS NOT NULL
      AND V_entryDate IS NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-24 : 05:40:57

Given that dates are in MM/DD/YYYY format, you can also do a checking

set dateformat mdy
select case when isdate(V_entryDate)=1 and len(V_entryDate)=10 then V_entryDate else NULL as entryDate from your_table

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 06:41:52

Madhi, I think dates are a mixture of MM/DD/YYYY and YYYY-MM-DD

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-24 : 06:48:39

quote:
Originally posted by Kristen

Madhi, I think dates are a mixture of MM/DD/YYYY and YYYY-MM-DD

Ok. In that case

set dateformat mdy
select case when isdate(V_entryDate)=1 and len(V_entryDate)=10 then V_entryDate else NULL as entryDate from your_table
where V_entryDate like '[0-9][0-9]/%'

set dateformat ymd
select case when isdate(V_entryDate)=1 and len(V_entryDate)=10 then replace(V_entryDate,'-','') else NULL as entryDate from your_table
where V_entryDate like '[0-9][0-9][0-9][0-9]-%'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:06:31

In any ways this is a bad way of storing dates. Firstly, using varchar field and then storing it in all different formats

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources