Convert GETDATE() to YYYYMMDD

Question

Convert GETDATE() to YYYYMMDD

Viewing 6 posts - 16 through 20 (of 20 total)

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

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

I like SSIS. For the most part we use it to move data between servers or to import/export data. Most data transformations, however, are done using SQL in Execute SQL tasks as T-SQL queries or inside stored procedures or using views. I'd do what the OP is try sourcing the data from SQL, not directly in SSIS. The only real transformations I have used in SSIS was when I used fuzzy lookups to match data in a quarterly import about 4 years ago. Made matching approximately similar data much easier than doing it in SQL.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

I guess that's why I don't use it... I don't need to match on approximate data. 🙂

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

Jeff Moden (12/22/2009)
I guess that's why I don't use it... I don't need to match on approximate data. 🙂

Working with the fuzzy lookup was actually quite fun. I can see how it could simplify some of the work we did in a DTS package at a former employer when it came to address cleanup and matching. If I remember correctly, we had a table of misspelled countries in which people had found numerous ways to misspell Canada, not to mention many of the other countries we shipped products to on a daily basis.

No, our COBOL based application did not have a table of country names to pull this from, in fact it was worse, international addresses were completely free form. Domestic orders were better as that was controlled from a table.

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 4

Here's the Expression syntax that will work if you're trying to accomplish this in a Derived Column transformation:

(DT_I4)((DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2))

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 5

(DT_STR,4,1252)DATEPART("YYYY",GETDATE()) + (DT_STR,2,1252)DATEPART("MM",GETDATE()) + (DT_STR,2,1252)DATEPART("DD",GETDATE())

This is the code that worked out for me

I am sorry for reposting it again. It will never happen.

Thanks very much guys for your replies and suggestions.

Thnak you very much and once again sorry for frustrating you guys.

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

Not a problem posting your code. It actually is different than was suggested before. It is always good to post your final solution. Others may benefit, so glad for the feedback.