Date Format - 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

Transact-SQL (2000)

Date Format

Author

Topic

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-20 : 23:47:15

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)

17689 Posts

Posted - 2006-09-21 : 00:02:54

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

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 01:25:37

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 02:34:18

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

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 11:12:58

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,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-21 : 11:51:47

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

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-21 : 11:58:57

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

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 12:00:07

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,

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-21 : 12:07:18

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-21 : 12:08:00

Did you read TG's reply?

Madhivanan

Failing to plan is Planning to fail

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 12:52:46

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-21 : 13:31:50

>>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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-21 : 13:55:41

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

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 14:41:00

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,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-21 : 15:02:56

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-21 : 15:03:32

At the risk of sounding like a broken record, did you read Jeff's latest reply?

Kristen
Test

22859 Posts

Posted - 2006-09-21 : 15:06:32

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources