SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Date Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

saidev
Posting Yak Master

101 Posts

Posted - 09/20/2006 :  23:47:15  Show Profile  Reply with Quote
Hi Guys,

I have a text file that i am importing into the table. The date is in the format of 060501(yymmdd) format. I want to the date in 010506(ddmmyy) format. can you guys help me how to change this format.
I am using VB.NET/ASP.NET
Thanks,

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 09/21/2006 :  00:02:54  Show Profile  Reply with Quote
the text file is in yymmdd format and you want to import into a table in ddmmyy format ?


KH

Go to Top of Page

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  01:25:37  Show Profile  Reply with Quote
quote:
Originally posted by khtan

the text file is in yymmdd format and you want to import into a table in ddmmyy format ?

That's right..!.. can you guys help me on this.
Thanks


KH



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 09/21/2006 :  02:34:18  Show Profile  Reply with Quote
what is the data type of the field in the table ? varchar or datetime ?

if it is varchar you can use substring() to change from yymmdd to ddmmyy easily.
if it is datetime you can use convert().
Check out BOL on the syntax.

Note : You should use datetime datatype to store date and not varchar


KH

Go to Top of Page

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  11:12:58  Show Profile  Reply with Quote
quote:
Originally posted by khtan

what is the data type of the field in the table ? varchar or datetime ?

if it is varchar you can use substring() to change from yymmdd to ddmmyy easily.
if it is datetime you can use convert().
Check out BOL on the syntax.

Note : You should use datetime datatype to store date and not varchar


KH





Hi,

The Data Type is Varchar.
Thanks,
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/21/2006 :  11:51:47  Show Profile  Reply with Quote
I'm curious, why do you want to use a varchar column to store a date?

Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/21/2006 :  11:58:57  Show Profile  Reply with Quote
assuming your "dates" are always 6 characters, just some basic string manipulation:
(look at string functions in Books Online)

select	right(dt,2) + substring(dt,3,2) + left(dt,2)
from	(
	select '060501' dt union
	select '060502' union
	select '060503'
	) source

output:
------ 
010506
020506
030506


Be One with the Optimizer
TG
Go to Top of Page

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  12:00:07  Show Profile  Reply with Quote
quote:
Originally posted by TG

I'm curious, why do you want to use a varchar column to store a date?

Be One with the Optimizer
TG



Because the text file is in (yymmdd) format. and also for some other imports we have to use only this format. so how to convert to ddmmyy format.
Thanks,
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/21/2006 :  12:07:18  Show Profile  Reply with Quote
quote:
Originally posted by saidev

quote:
Originally posted by TG

I'm curious, why do you want to use a varchar column to store a date?

Be One with the Optimizer
TG



Because the text file is in (yymmdd) format. and also for some other imports we have to use only this format. so how to convert to ddmmyy format.
Thanks,



So, why do you want to use a varchar column to store a date?


CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/21/2006 :  12:08:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Did you read TG's reply?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  12:52:46  Show Profile  Reply with Quote
quote:
Originally posted by TG

assuming your "dates" are always 6 characters, just some basic string manipulation:
(look at string functions in Books Online)

select	right(dt,2) + substring(dt,3,2) + left(dt,2)
from	(
	select '060501' dt union
	select '060502' union
	select '060503'
	) source

output:
------ 
010506
020506
030506


Be One with the Optimizer
TG




Hi,
I have 5000 Records in a table that need to be changed in this format. In this case how to do this.
Thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/21/2006 :  13:31:50  Show Profile  Reply with Quote
>>I have 5000 Records in a table that need to be changed in this format. In this case how to do this.

I guess I'm not clear what you need help with exactly. I think the method to transform a varchar format from yymmdd to ddmmyy is pretty clear (above). Are you asking about a general strategy, ie: create a new table, or add a new column to existing table, or update existing column?

I'm confused by your answer to the question of why you are deciding to use a varchar column to store a date. Just because you get the data as varchar doesn't mean you have to store it that way. Do you understand the advantages of storing date concepts as a datetime datatype?

Sometime when you are in your learning/studying mode, go through the topics and links on this thread. They are a great source for understanding how to take advantage of datetime datatypes.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Be One with the Optimizer
TG
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/21/2006 :  13:55:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
The main point is this: If you store your data as an actual date with the proper datetime datatype, you know that:

a) it is a valid date
b) you can use functions like DateAdd, Month, DateDiff, Year, etc on the date
c) YOu can format that date any way you want when you output it somewhere
d) When you return data to a client or a report, it will be the proper and expected datatype
and
e) It will sort and compare correctly unlike certain date formats like mm/dd/yyyy.




- Jeff

Edited by - jsmith8858 on 09/21/2006 13:56:48
Go to Top of Page

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  14:41:00  Show Profile  Reply with Quote
Hi Guys,

When i import the data from text file into the table it is already in the format 061225(yymmdd) Format. I want it in 122506(mmddyy) Format. i have to use only Varchar data type inorder to export the data. So when i import the data into the table the date field has 5000 records in it. now i want to change this yymmdd format to mmddyy format. I am using VB.NET/ASP.NET and SQL SERVER 2000
is this clear guys.
Thanks,
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35951 Posts

Posted - 09/21/2006 :  15:02:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by saidev

i have to use only Varchar data type inorder to export the data.


Incorrect. You may think you need to use varchar data type, but you don't.

Tara Kizer
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 09/21/2006 :  15:03:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
At the risk of sounding like a broken record, did you read Jeff's latest reply?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/21/2006 :  15:06:32  Show Profile  Reply with Quote
kish kanna,

I don't mean to sound rude, but you aren't listening mate.

Import the yymmdd text string into a column with a datatype of datetime.

If you want to display the value back to the user format it in mmddyy (or any other format you want).

The internal storage SQL Server uses for a datetime will not be something you need to worry about, but you will have all the benefits that Jeff pointed out.

SET DATEFORMAT YMD
... import data in yymmdd format into the table

To display:

SELECT CONVERT(varchar(8), MyDateColumn, 1)
or
SELECT REPLACE(CONVERT(varchar(8), MyDateColumn, 1), '/', '')

but better still format the data using your Front End Application - which probably has all sorts of formatting abilities, and probably a default "template" you can use for all date displaying.

If you HAVE to format it in SQL then just write a Function that encapsulates the conversion that suits you, and user that to display the date in your preferred format.

If you store it as a text string you will lose all the benefits of it being stored as a native date type - which the learned folk here know will be a downstream problem for you, and are thus trying to encourage you to do it now.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000