How reformat the date - Newbee

Question

Post reply

How reformat the date - Newbee

nshah6

Valued Member

Points: 61
More actions
October 28, 2010 at 1:41 pm

#228565

All,
Thank you for your time. Please note, I didn't know if this is the right forum to post my question, so I apologize if I am wrong.
The issue is the table A has date column as character and the values are below:
Date
4/17/2010
10/05/2010
8/3/2010
I can use sub-string function to re-format the date as yyyy-mm-dd only thing is it will not work as the date value is not in consistent format.
I have to create flat file with date format in yyyy-mm-dd.
I am using MS Visual Studio to create the SSIS Package to extract this table.
Thank you !

Viewing 15 posts - 1 through 15 (of 15 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

select convert(datetime, DateColumn)

from MyTable;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

nshah6 Valued Member Points: 61 More actions · Answer 2

Tried that. But I don't need time part. Just the date in yyyy-mm-dd

Thanks for response.

nshah6 Valued Member Points: 61 More actions · Answer 3

Further more, I want date to populate consistently.

so in above example, the output should appear like

Date

04/17/2010

10/05/2010

08/03/2010

WayneS SSC Guru Points: 95461 More actions · Answer 4

GSquared (10/28/2010)
select convert(datetime, DateColumn)
from MyTable;

nshah6 (10/28/2010)
Tried that. But I don't need time part. Just the date in yyyy-mm-dd
Thanks for response.

SELECT CONVERT(CHAR(10), CONVERT(datetime, DateColumn), 120);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

nshah6 Valued Member Points: 61 More actions · Answer 5

Thank you !

You guys are Great. I really appreciate this ! 🙂

I consider this problem RESOLVED !

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

nshah6 (10/28/2010)
Thank you !
You guys are Great. I really appreciate this ! 🙂
I consider this problem RESOLVED !

Maybe so but you should consider it to be just a temporary patch. 😉 The heavy hitters on this forum will tell you 2 things about what you have... 1) Never store dates in a table as VARCHAR or CHAR (too many reasons to list here for that) and 2) you really shouldn't format dates for display using T-SQL. It should be left up to either the GUI or whatever reporting tool you may be using so that if the code ends up in a different country, the local date/time formatting on the local PC's can format the date correctly for whatever part of the world it happens to be in.

Seriously... consider changing the datatype of that date column to DATETIME. 🙂

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jeff Moden (10/28/2010)
nshah6 (10/28/2010)
Thank you !
You guys are Great. I really appreciate this ! 🙂
I consider this problem RESOLVED !
Maybe so but you should consider it to be just a temporary patch. 😉 The heavy hitters on this forum will tell you 2 things about what you have... 1) Never store dates in a table as VARCHAR or CHAR (too many reasons to list here for that) and 2) you really shouldn't format dates for display using T-SQL. It should be left up to either the GUI or whatever reporting tool you may be using so that if the code ends up in a different country, the local date/time formatting on the local PC's can format the date correctly for whatever part of the world it happens to be in.
Seriously... consider changing the datatype of that date column to DATETIME. 🙂

Jeff,

He is creating a flat file using SSIS and needs a consistant format. Seems logical to me in this particular case.

WayneS SSC Guru Points: 95461 More actions · Answer 8

Lynn Pettis (10/28/2010)
Jeff Moden (10/28/2010)
nshah6 (10/28/2010)
Thank you !
You guys are Great. I really appreciate this ! 🙂
I consider this problem RESOLVED !
Maybe so but you should consider it to be just a temporary patch. 😉 The heavy hitters on this forum will tell you 2 things about what you have... 1) Never store dates in a table as VARCHAR or CHAR (too many reasons to list here for that) and 2) you really shouldn't format dates for display using T-SQL. It should be left up to either the GUI or whatever reporting tool you may be using so that if the code ends up in a different country, the local date/time formatting on the local PC's can format the date correctly for whatever part of the world it happens to be in.
Seriously... consider changing the datatype of that date column to DATETIME. 🙂
Jeff,
He is creating a flat file using SSIS and needs a consistant format. Seems logical to me in this particular case.

Lynn,

Jeff's right. From the OP's first post:

The issue is the table A has date column as character and the values are below:

Granted, this is a tangent for the OP's issue, but this is still an issue.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004643 More actions · Answer 9

{Flat file} Ah... so I see. I missed that in the OP. Thanks, Lynn.

It really is still an issue that the original data has a non-DATETIME column that stores dates, though.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

WayneS (10/28/2010)
Lynn Pettis (10/28/2010)
Jeff Moden (10/28/2010)
nshah6 (10/28/2010)
Thank you !
You guys are Great. I really appreciate this ! 🙂
I consider this problem RESOLVED !
Maybe so but you should consider it to be just a temporary patch. 😉 The heavy hitters on this forum will tell you 2 things about what you have... 1) Never store dates in a table as VARCHAR or CHAR (too many reasons to list here for that) and 2) you really shouldn't format dates for display using T-SQL. It should be left up to either the GUI or whatever reporting tool you may be using so that if the code ends up in a different country, the local date/time formatting on the local PC's can format the date correctly for whatever part of the world it happens to be in.
Seriously... consider changing the datatype of that date column to DATETIME. 🙂
Jeff,
He is creating a flat file using SSIS and needs a consistant format. Seems logical to me in this particular case.
Lynn,
Jeff's right. From the OP's first post:
The issue is the table A has date column as character and the values are below:
Granted, this is a tangent for the OP's issue, but this is still an issue.

I agree that dates should be stored as dates, that isn't my issue. The issue was Jeff's statement that the formatting should be done in the GUI or reporting tool. The OP is generating a flat file, so in my opinion formatting the date in T-SQL is acceptable as there is no GUI or reporting tool.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Also, I do agree that if there is a GUI or reporting tool (SSRS, Crystal, etc) that formatting of dates should occur there (as well as for all other data).

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 12

Jeff Moden (10/28/2010)
nshah6 (10/28/2010)
Thank you !
You guys are Great. I really appreciate this ! 🙂
I consider this problem RESOLVED !
Maybe so but you should consider it to be just a temporary patch. 😉 The heavy hitters on this forum will tell you 2 things about what you have... 1) Never store dates in a table as VARCHAR or CHAR (too many reasons to list here for that) and 2) you really shouldn't format dates for display using T-SQL. It should be left up to either the GUI or whatever reporting tool you may be using so that if the code ends up in a different country, the local date/time formatting on the local PC's can format the date correctly for whatever part of the world it happens to be in.
Seriously... consider changing the datatype of that date column to DATETIME. 🙂

the SQL Server/Windows Devs are guilty of this. if you run performance monitor and dump the data to a database via ODBC it creates the tables for you with a horrific schema. varchar for date data. and some varchar (4000) columns that i had to manually alter so i could index them

Jeff Moden SSC Guru Points: 1004643 More actions · Answer 13

Lynn Pettis (10/29/2010)
The OP is generating a flat file, so in my opinion formatting the date in T-SQL is acceptable as there is no GUI or reporting tool.

Just for the record, I absolutely agree with that usage of formatting as Lynn said. I just missed it in the original post.

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

WayneS SSC Guru Points: 95461 More actions · Answer 14

Lynn Pettis (10/29/2010)
I agree that dates should be stored as dates, that isn't my issue. The issue was Jeff's statement that the formatting should be done in the GUI or reporting tool. The OP is generating a flat file, so in my opinion formatting the date in T-SQL is acceptable as there is no GUI or reporting tool.

Gotcha - I was reading your reply as to his first point, not his second. We're all on the same page now!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2