date conversion problem - 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 conversion problem

Author

Topic

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-14 : 16:28:46

I have a column that has the date in the format YY / MM /DD. I want to convert it to the standard MM / DD / YY format the ms sql server recognizes in the datetime type. If there an easy way for me to convert? I have 1300 records.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 16:33:14

Is the field type stored as datetime?

If so, you can format the output of the field any way you look (how it is stored in the database doesn't change), but answer your question depends on the field's datatype (in part)

Poor planning on your part does not constitute an emergency on my part.

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 00:44:27

no, the date is not stored as date time

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 07:00:54

quote:
Originally posted by kifeda

no, the date is not stored as date time

Then you should store as datetime data type. it will make thing much easier. Then the presentation of the date is just a simple formatting command on your front end application

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-15 : 07:05:28

i think that is what he is asking.

He has a column (char 8) and needs to make it a datetime. The char(8) is in the format 'YY/MM/DD'.
At least, that is how i read it. your reply is correct however, in stating that FORMATTING IS DONE IN THE FRONT END.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 07:22:03

quote:
Originally posted by kifeda

no, the date is not stored as date time

Looks like the date is stored in varchar

just convert to datetime using convert(datetime, yourdatecol)

KH
[spoiler]Time is always against us[/spoiler]

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 10:37:34

yes, don is exactly right. I have a column (it's actually varchar(50) now) and I want to convert it to date time. the problems is that if I have a date like 07/10/23 for October 23rd 2007 and then I import tha date to MS SQL server and convert the field to date time, it will not preserve the format. it will chaneg it to July 10, 2023 or something like that. This is my problem. So I'll try to use the convert(datetime, yourdatecol) like htan suggested and see if that works.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 10:38:52

maybe this will help http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

KH
[spoiler]Time is always against us[/spoiler]

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 11:07:06

khtan, I tried to read through the example and I simply got lost. I'm not a DBA. I need to know wher to plug the name of my column and table in. That's kind of a bit much for me. Do you think you can explain the how to use the convert fucntion you meantioned earlier? Like show me how I would run it in query analyser?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:11:11

the function will convert your date in string to datetime datatype.


select your_date_col_in_varchar, dbo.proper_date(your_date_col_in_varchar)
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 11:27:34

thanks. I did a little home work and learned about userdefine functions. However, it still does not work. use the date 07/10/23. This is October 23, 2007. However, even with the user defined function that I now have, it still sees it as 07/10/2023. Any clues?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:33:15

well, that UDF was not expecting your date is YY/MM/DD

KH
[spoiler]Time is always against us[/spoiler]

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 11:48:12

any suggestions for how to change it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:50:51

let's wait for Madhivanan's reply

KH
[spoiler]Time is always against us[/spoiler]

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 12:06:41

okay

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 12:26:38

I'm almost there!

I have 1300 hundred records and I know that the date are all in the 2000. So what I did was use the convert(datetime, yourdatecol) function and add the 20 to the year date. Remember all my dates are in the 07 / 10 / 23 format. So if I put 2007/10/23 in the convert date function, it will convert it to 10/23/2007 which is what I want. The problem was that I don't have the year in a four digit format. I only have it as a two digit. So, what I did was add the 20 to the begining of the string and then try to convert it.

It did:

Select dtstartdate, convert(datetime, '20'+dtdtdtstartDate) as newStartDate from sheet3$

This work....almost. I keep getting an arithmetic overflow error converting expression to data type datetime. I thought this was because I might have had some of the dates put in wrong, but they look correct to me. Any takes? QA will return rows and then throw the error. It appears that it always errors right arounf 44 or 45 records.

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 12:43:11

Well if you know all your years are >= 2000 and <= 2099. What about this?


select 
	convert(datetime, 
		'20'
		+substring(dtdtdtstartDate,1,2)
		+substring(dtdtdtstartDate,4,2)
		+substring(dtdtdtstartDate,7,2)
	, 102)

;-]... Quack Waddle

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-15 : 16:20:15

this is weird. I was able to convert both columns, but now I just can't get it into a new table. I keep getting that arithmetic overflow error. It's driving me crazy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-15 : 16:56:16

Move than likely, you have rows where you have invalid months, days, or month/day combinations.
June 31 perhaps?

Just another reason why dates should be stored in datetime columns.

CODO ERGO SUM

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-16 : 04:36:54

Hunt down the problem rows.


select 
	dtdtdtstartDate, 
		'20'
		+substring(dtdtdtstartDate,1,2)
		+substring(dtdtdtstartDate,4,2)
		+substring(dtdtdtstartDate,7,2)
	as isoDate 
from
        thetablename 
where 
        ISDATE(
               '20'
		+substring(dtdtdtstartDate,1,2)
		+substring(dtdtdtstartDate,4,2)
		+substring(dtdtdtstartDate,7,2)
               ) = 0

;-]... Quack Waddle

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 03:19:44

quote:
Originally posted by khtan

let's wait for Madhivanan's reply

KH
[spoiler]Time is always against us[/spoiler]

I already answered there

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources