date formats - SQL Server Forums

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

date formats

Author

Topic

InNomina
Starting Member

40 Posts

Posted - 2012-02-28 : 18:31:21

I inherited a database where the date column has 2 types of date formats < no idea why>

they are:
a. 01JAN92
b. 95

I need to reformat all 24000 records to a standard of mm/dd/yyyy

Help!

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 18:35:37

for dates like 95 what value does it represent? 01-01-1995?

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

InNomina
Starting Member

40 Posts

Posted - 2012-02-28 : 18:43:13

quote:
Originally posted by visakh16

for dates like 95 what value does it represent? 01-01-1995?

Sorry yes...that would be 01/01/1995

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 19:07:00

[code]SELECT CONVERT(datetime,case when len(field)=7 then stuff(stuff(field,3,' ',0),6,' ',0) else '01 JAN ' + field end,106) from table [/code]

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

InNomina
Starting Member

40 Posts

Posted - 2012-02-28 : 19:46:51

quote:

Originally posted by visakh16

SELECT CONVERT(datetime,case when len(field)=7 then stuff(stuff(field,3,' ',0),6,' ',0) else '01 JAN ' + field end,106) from table

Thank you!
When I add the appropriate info and run I get....

Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 3 of stuff function.

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 19:51:47

[code]SELECT CONVERT(datetime,case when len(field)=7 then stuff(stuff(field,3,0,' '),6,0,' ') else '01 JAN ' + field end,106) from table[/code]

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

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 10:57:07

quote:

Originally posted by visakh16

SELECT CONVERT(datetime,case when len(field)=7 then stuff(stuff(field,3,0,' '),6,0,' ') else '01 JAN ' + field end,106) from table

Thank you for the help!

This is what I get when I run it...

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

-------------------------
"If you never fail, you're not trying hard enough"

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:04:22

it is quite possible that you have other types of Data in there

And What's the actual name of the Column..it's not "field" is it?

SELECT DISTINCT [field]
FROM table
WHERE LEN([field]) < 7
OR LEN([field]) > 7

What does that give you? Post some of the results please

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:31:24

quote:
Originally posted by InNomina

quote:
Originally posted by visakh16

SELECT CONVERT(datetime,case when len(field)=7 then stuff(stuff(field,3,0,' '),6,0,' ') else '01 JAN ' + field end,106) from table

Thank you for the help!

This is what I get when I run it...

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

-------------------------
"If you never fail, you're not trying hard enough"

this is whole problem of mixing different date formats inside same field

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

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 12:01:03

quote:
Originally posted by X002548

it is quite possible that you have other types of Data in there

And What's the actual name of the Column..it's not "field" is it?

SELECT DISTINCT [field]
FROM table
WHERE LEN([field]) < 7
OR LEN([field]) > 7

What does that give you? Post some of the results please

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

There are 2 formats, the 2 digit year- 95 and 07AUG98 in that column called OpenDate

-------------------------
"If you never fail, you're not trying hard enough"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 12:06:26

what does this return?


SELECT case when len(field)=7 then stuff(stuff(field,3,0,' '),6,0,' ') else '01 JAN ' + field end from table

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 12:38:37

Did you run my Code, or are you just telling us what you know to be true

Those 2 thing may not be the same

Can you please post the results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 14:11:32

quote:
Originally posted by visakh16

what does this return?

SELECT case when len(field)=7 then stuff(stuff(field,3,0,' '),6,0,' ') else '01 JAN ' + field end from table

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

01 JA N92
01 JAN 95
06 JU N92
06 JU N93
01 JA N95
02 FE B92
10 OC T93
01 JAN 95
01 JAN 95
01 JAN 95
01 JAN 95
02 FE B93
01 JAN 96
04 AP R94
12 DE C92
09 SE P94
01 JA N94
01 JAN 95

-------------------------
"If you never fail, you're not trying hard enough"

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 14:14:30

quote:
Originally posted by InNomina

[quote]Originally posted by X002548

it is quite possible that you have other types of Data in there

And What's the actual name of the Column..it's not "field" is it?

SELECT DISTINCT [field]
FROM table
WHERE LEN([field]) < 7
OR LEN([field]) > 7

What does that give you? Post some of the results please
Brett

Sorry, it Returns...

OpenDate
05/96
8-3-10
89
93
2009
79
DEC95
3/11
00
9/96
95
2006
8/7/07
83
90
80
JUN96
2007
12
97
09/96
04/11
2008
03
06/11
81
92
200
2001
11
21
9
87
09
03/99
77
06
84
2011
7
78
01
000096
07
CA
08/96
04
82
2/10/201
99
23
02/10/12
10
01/01/08
FEB 97
94
2003
2000
85
7/2010
96
05/11
DEC 99
07/01/11
08
91
03/96
2010
04/95
2002
5
AUG 96
000098
05
20
98
NOV 99
8/10
02
88
000097

-------------------------
"If you never fail, you're not trying hard enough"

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:20:39

Which is point EXACTLY

You have Lots of different Formats other than 01JAN92 and 95

How do you propose to handle the others?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:29:08

I wish I asked you to order them by LEN then Field

This is the category of stuff you have. Each one needs to be addressed by itself

-- Who knows what these are
5
7
9
-- 00 is just bogus
00
-- Could these be Months?
01
02
03
04
05
06
07
08
09
10
11
12
-- Could these by Year?
20
21
23
77
78
79
80
81
82
83
84
85
87
88
89
90
91
92
93
94
95
96
97
98
99
-- Who knows what this is
CA
-- Bogus
200
-- The year?
2000
2001
2002
2003
2006
2007
2008
2009
2010
2011
-- Actual Dates in mm/yy
3/11
8/10
9/96
03/96
03/99
04/11
04/95
05/11
05/96
06/11
08/96
09/96
-- MONTH/YY
DEC95
JUN96
-- Who knows
000096
000097
000098
-- some other formats
7/2010
8-3-10
8/7/07
-- MONTH/YY with space
AUG 96
DEC 99
FEB 97
NOV 99
-- Other date formats
01/01/08
02/10/12
07/01/11
-- le Garbage?
2/10/201

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 14:30:42

quote:
Originally posted by X002548

Which is point EXACTLY

You have Lots of different Formats other than 01JAN92 and 95

How do you propose to handle the others?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

I see that now....Sigh.....thank you much for the time. I will need to consider how I want to tackle 81 different formats.

-------------------------
"If you never fail, you're not trying hard enough"

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:33:31

When someone says X, you say, SHOW ME

Believe NO ONE

Trust but Verify - RR

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 14:34:04

quote:
Originally posted by X002548

I wish I asked you to order them by LEN then Field

This is the category of stuff you have. Each one needs to be addressed by itself

-- Who knows what these are
5
7
9
-- 00 is just bogus
00
-- Could these be Months?
01
02
03
04
05
06
07
08
09
10
11
12
-- Could these by Year?
20
21
23
77
78
79
80
81
82
83
84
85
87
88
89
90
91
92
93
94
95
96
97
98
99
-- Who knows what this is
CA
-- Bogus
200
-- The year?
2000
2001
2002
2003
2006
2007
2008
2009
2010
2011
-- Actual Dates in mm/yy
3/11
8/10
9/96
03/96
03/99
04/11
04/95
05/11
05/96
06/11
08/96
09/96
-- MONTH/YY
DEC95
JUN96
-- Who knows
000096
000097
000098
-- some other formats
7/2010
8-3-10
8/7/07
-- MONTH/YY with space
AUG 96
DEC 99
FEB 97
NOV 99
-- Other date formats
01/01/08
02/10/12
07/01/11
-- le Garbage?
2/10/201

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

I am told it's all in years, 00 would be 2000, single digits no idea......sigh....ok thank you very much for the responses.

-------------------------
"If you never fail, you're not trying hard enough"

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:41:15

So go back to the bozo with these results and ask them, "Now what?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

InNomina
Starting Member

40 Posts

Posted - 2012-02-29 : 14:46:37

Thank you all for your input, I believe I have enough to start piecing together my solution.

So my starter was to create a new column and have sql convert everything it could convert which left me with far less to do :-)

UPDATE Folders SET OPENDATE1 = CASE WHEN ISDATE(OpenDate) > 0 THEN cast(OpenDate as datetime) END

Thanks again!
-------------------------
"If you never fail, you're not trying hard enough"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources