INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date Format issue

Date Format issue

(OP)
Hi

I am exporting data from SQL server to CSV. One of these fields is a time and date field.

I want the format to be just dd/mm/yyy. I have gone into advance properties and selected the date filed and changed the DataType to date[DT_Date]
I click OK. This does not work as it keeps reverting back to database timestamp [DB_DbTimeStamp]

Could someone advise please how I get my date to the format I require please.
Thanks

RE: Date Format issue

(OP)
HI

I have created a convert command in the SQL view which outputs the date how I want it now.

If there is a simple way in SSIS I would still be interested to know how

Thanks

RE: Date Format issue

the correct and easiest way is to change it on the select to the format you wish.
Alternative method is to create a derived column or data conversion step on your dataflow and convert the datetime to a date.

The above will give you the value as a date - but does not ensure that the format on the csv file will be the one you asked for as that is locale dependent. If your requirement is to have the date always on a particular format then convert manually to a string. again it is easier to do this in tsql.

And you should consider that using a ISO format for the date may be your best option as it will be read and converted by almost any software.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Date Format issue

(OP)
Hi

One issue I do have is I have my SQL to have 08:00 and 17:30
This exported from SSIS into CSV no problem.

I have now been informed the filed must be a decimal place and this as made the output to CSV incorrect.
I change it in SQL to 08.00 and 17.30

The out put in CSV file as 8 and 17.3

It goes to excel for CSV. If I view the result in SSIS it looks ok. Is there a way round this or is it how excel handles the import.

Thanks

RE: Date Format issue

never ever ever use Excel to validate that your file is on the correct format.

edit it with a normal text editor - if it is correct there and according to the specifications then that is what matters.

Excel will do all type of guessing and formatting and should not be used for testing.

Now if the final target of an extract is Excel you may need to extract data in a particular way and you will probably also need to run a Excel vba macro over the data once its loaded onto excel to format it according to the requirements. Not the case here I think.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Date Format issue

(OP)
Hi

yes I must agree. However, when I create .CSV from SSIS it automatically creates the CSV in Excel.
I have changed the extension to .txt and opens up in Notebook and the format is correst. So obvioulsy an excel issue.
Therefore how do i get the file to be .CSV without Excel being the program selected to make the CSV file?

Thanks

RE: Date Format issue

you are mixing things.

one thing is creating the CSV file - that has nothing to do with Excel and you don't even need to have Excel on the computer where the SSIS package runs.

Another thing is opening the file for processing - by default if you have Excel installed on the computer if you double click on the csv file it will open with Excel. But this has nothing to do with SSIS.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Date Format issue

To illustrate what fredericofonseca is saying, instead of double clicking the csv file where it automatically opens excel(that is because the csv extension is associated with excel), open up a new workbook of excel and import the csv (Data\From Text) - go through the wizard (choose delimited and comma as delimiter) - make the column that has the time a TEXT data format in the wizard. You will then see that the zeros will remain there.

The reason they are disappearing is because when you double click the csv and excel is automatically opening it, it sees these values as numbers and is automatically removing the zeros.

Thanks

Michael

RE: Date Format issue

(OP)
Hi

Yes of course thank you for the explanation and quite correct I was getting confused with the actual outcome.
The file itself will be imported to another business system so I am assuming their import ruotine will deal with this and I will be testing with them tomorrow.

Thanks for the replys and assistance.

RE: Date Format issue

(OP)
Hi

Ok I have been using opening the CSV file through Excel import routine. We have come across an issue with a filed we are exporting.

The field holds special instructions and within this there as been data entered with carriage returns.

When this is the case the field is then forcing an extra row. In the image below you can see that the line Shiplap always SG2 should actually be a part of the row and just after the part which starts with *Must Change all price to per metre*



Is there a way to prevent this field from doing this perhaps in the Advanced part in the flat file connection manager editor perhaps. Thanks

RE: Date Format issue

(OP)
Hi

Its ok solved this within SQL using REPLACE(REPLACE(dbo.OrderHeader.SpecialInstructions, CHAR(13), ' '), CHAR(10), '')

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close