Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

Date time conversion in SSIS

I am extracting data from Sql table's and exporting it to Excel, i am using data conversion transformation between source and target., in data conversion transformation I am using DT_Date to convert the data, when I use DT_date I am getting result as date + time.
However I just want date in MM-DD-YYYY format.

How can I achieve this using data conversion transformation?
0
Sriv
Asked:
Sriv
  • 3
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
try..

(DT_DBDATE)(DT_DATE)GETDATE()

Use yourcolumnName instead of GETDATE()
0
 
SrivAuthor Commented:
Pawan  I am using data conversion transformation and I dont think we can write expressions in Data conversion transformtion  is that right?

Please correct me if I am wrong
0
 
Pawan KumarDatabase ExpertCommented:
Try...this then

1. Goto advanced editor of data conversion transformation -> Input & Output Properties -> YourDate Column
2. FastParse = True <<  in Custom Properties >>
3. Change DataType of yourDate columne to [DT_DBDATE]

Hope it helps !!
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Arifhusen AnsariBusiness Intelligence AnalystCommented:
may be i don't know what's your exact requirement is?

Custom formatting is not possible in SSIS.

I would suggest you to get the date in the format you want, directly from sql database.

SELECT FORMAT(getdate(),'MM-dd-yyyy')

Open in new window


In your case

SELECT FORMAT(yourtablecolumn,'MM-dd-yyyy')

Open in new window


And you don't even need to use the data conversion in the package.

May be you need to write query rather that loading data directly from table.

Hope it will help.
0
 
Pawan KumarDatabase ExpertCommented:
Yes this is good option by Arifhusen.

Also note that Format is only available in SQL 2012+. Format converts the DATE column into string. So don't use format function. It should only used for display only purpose.

You should use

SELECT
CAST(yourtablecolumn AS DATE) yourtablecolumn
FROM TABLE

Hope it helps !
0
 
SrivAuthor Commented:
Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now