Varchar to DateTime - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Varchar to DateTime

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 12:52:14

I have recived a nastly little file in witch one field is supposed to represent mmyy. Unfortunately as a numeric field it recived things like 0, 399 , 0702,null ext.

I have converted into a varchar field and cleaned up the digits.

UPDATE dbo.MaCust
SET DateAddold = RIGHT('0000' + [MM/YY Added], 4)

What I need to do now is change this Varchar into a DateTime preferably using the 1st of the month for a day field. I have had 0 luck with using convert for this.

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-08 : 12:58:36

I don't understand what you mean by "using the 1st of the month for a day field". Could you explain that? Also, could you show us some sample data of what the data looks like now as a varchar?

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 13:05:10

1st day of the month as in 02/01/03.

DateAddold Should Be
0602 ---- 06/01/2002
1002 ---- 10/01/2002
0998 ---- 09/01/1998
0901 ---- 09/01/2001
0502 ---- 05/01/2002
1101 ---- 11/01/2001

Jim

Edited by - JimL on 05/08/2003 13:06:23

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-08 : 13:12:50

CREATE TABLE Test
(
DateAddOld VARCHAR(4) NOT NULL
)

INSERT INTO Test VALUES('0602')
INSERT INTO Test VALUES('1002')
INSERT INTO Test VALUES('0998')

SELECT SUBSTRING(DateAddOld, 1, 2) + '/01/' + SUBSTRING(DateAddOld, 3, 2)
FROM Test

DROP TABLE Test

Now the data is in the correct format, so you'll want to update the data, then modify the column to datetime.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-08 : 13:14:14

How about:


SELECT CONVERT(datetime,
	 SUBSTRING(RIGHT('00'+'303',4),1,2)
	+'/01/'
	+SUBSTRING(RIGHT('00'+'303',4),3,2)
	)

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-08 : 13:17:16

scary Tara....

Part of his problem is that he won't get leading zeroes for the month.

I guess you have to "assume" that he will for the year, I mean Jim, would you ever get "12" for January 1st, 2002?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-08 : 13:21:26

quote:

scary Tara....

Part of his problem is that he won't get leading zeroes for the month.

I guess you have to "assume" that he will for the year, I mean Jim, would you ever get "12" for January 1st, 2002?

Brett

8-)

Why won't he having leading zeroes for the month? Doesn't his sample data show that the leading zero exists in his data? What am I missing?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-08 : 13:24:05

Well not in his original post:

quote:

Unfortunately as a numeric field it recived things like 0, 399 , 0702,null ext.

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 13:24:55

It appears that I lucked out they acctually got the year right.

great work folks.

Tarra .... I would have never thought to use a substring. my head was stuck in date format.

Thanks

Jim

Edited by - JimL on 05/08/2003 13:25:57

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-08 : 13:25:23

AAhhhh! I thought I was just going insane. --referring to Brett's post

Tara

Edited by - tduggan on 05/08/2003 13:26:25

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 13:27:56

Nope but I worked on this for an Hour and I was going NUTS.

Thanks Again.

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-08 : 13:27:57

quote:

It appears that I lucked out they acctually got the year right.

great work folks.

Tarra .... I would have never thought to use a substring. my head was stuck in date format.

Thanks

Jim

Edited by - JimL on 05/08/2003 13:25:57

The problem with datetime is that it expects certain formats. So if you have data that is not in a format that datetime will accept, then you have to manipulate the data. SUBSTRING was used so that we could put /01/ in the data.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources