Coverting Date formats

Question

Post reply

Coverting Date formats

Susan-322874

SSCrazy

Points: 2300
More actions
August 8, 2007 at 5:01 pm

#174123

I currently have a date column showing up as 'Mar 20 2007 3:57pm' and need it to be
'2007/03/20' so I can import it into a third party application.
Can I make that modification directly to the column or do I need to do a convert while exporting the data?
If I can do it at the column level, would it be done via an update statement? or alter on the table?
Thanks in advance
Susan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What is datatype for that column?

_____________
Code for TallyGenerator

Susan-322874 SSCrazy Points: 2300 More actions · Answer 2

Two of them are varchar (not sure why), the other two are datetime.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Like this...

SELECT CONVERT(CHAR(10),CAST('Mar 20 2007 3:57pm' AS DATETIME),111)

Of course, you would sub a couple of things to use against a table...

SELECT CONVERT(CHAR(10),CAST(yourdatestringcolumn AS DATETIME),111)

FROM yourtable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

In first post you mentioned only one column.

Which one?

_____________
Code for TallyGenerator

Susan-322874 SSCrazy Points: 2300 More actions · Answer 5

In first post you mentioned only one column.

Which one?

Turns out we need to modify all 4 date columns (not part of initial request).

I know I can get it out in that format via convert, but was wondering

if I can actually update/modify the table to always have that date format.

Or do I handle it by creating a view with the convert statement in the select?

Thanks again

Susan

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

DateTime columns have no format... the dates are actually stored as a special form of FLOAT (BOL says 2 INTs but that's not quite right). Changing the column to a VARCHAR would certainly preserve the format but would kill you on sorts and other date manipulations.

If it's for a GUI, let the GUI format the date according to local settings. If it's for reports or batch files, it's no biggee to calc the format and still enjoy all the benefits of the DateTime data type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aureolin Hall of Fame Points: 3520 More actions · Answer 7

Actually, I'd go the other way. Convert the varchar columns to datetime fields. This will give you a certainty that the dates are all in the same format, and give you a chance to fix those that aren't. Then, you can do a simple convert to get the output format you want.

The concern here is, of course, that there's "anomalous" data in the varchar fields and that any attempt to do output formatting will break down on those records.

Steve G.

Susan-322874 SSCrazy Points: 2300 More actions · Answer 8

Susan-322874

SSCrazy

Points: 2300

August 11, 2007 at 8:16 pm

#726301

Great, thanks for the info

Susan