Avatar of gwarcher
gwarcher
 asked on

Convert Year string to date

I have a table that has a birthdate field inside of it.  The vast majority of these records are just the year (example:  "1975")  There are a few records that have an actual date stamp (example:  "04/04/1980")

Here is my issue.

I am running this through ETL.  I need to either trim the date to just have the year marker or add "01/01" to the existing years to standardize.  I have tried script components to append the 01/01 and I have tried data conversion and derived columns, none of which are working.  Is there something else I may be able to do to approach this?  

Thanks
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
SOLUTION
Qlemo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]

Scott Pletcher

Except that article tells you to use mm/dd/yyyy format, which can cause errors (either because you forgot to include a conversion code or used the wrong one).

Instead, always use 'YYYYMMDD' and you NEVER have to specify a format code for a date, EVER.
gwarcher

ASKER
Thank you, It worked as a case statement.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Guy Hengel [angelIII / a3]

Scott,
where does my article tell to use mm/dd/yyyy format?
given, it does show examples using that format, but I don't tell that you HAVE to use that format?
Scott Pletcher

Here's the quote from your aricle:

"
Enough playing; let's see "how to do it the right way".
in MS SQL Server, use the CONVERT function:

INSERT INTO orders ( order_date , ordering_client_id )
VALUES ( CONVERT(DATETIME, '08/31/2009', 101 ), 1 )
"


"How to do it the right way" is telling them to use that format, isn't it?
You NEVER mention using 'YYYYMMDD' instead.

We have offices around the world, as do other companies now I'm sure.
Try this on your local SQL:

SET LANGUAGE german

SELECT CAST('20120117' AS datetime)

SELECT CAST('2012-01-17' AS datetime)

Yes, you can use CONVERT on the second one, but then everyone who needs to use the code has to memorize or look up what style "101" -- or whatever other code is the default for that particular location -- means.  That is, if you get code from, say, Europe, it will NOT use "101", it will use some code you're not familiar with and have to look up, and vice versa.

'YYYYMMDD' is universal and will never have to be changed to work anywhere in the world and no one ever has to waste time looking up date format codes.
Guy Hengel [angelIII / a3]

I agree with that on a pure engineering level 100%.
and I won't debate long about the format etc.

the main point of the article is:
* make sure you convert/cast your "string" into date at the right time, and avoid implicit conversions.
* it's for beginners, not for "advanced" people like you and me, to get them aware of what issues you can run into if you don't handle dates correctly.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

Even more vital for beginners to learn correctly from the start -- poor coding is much harder to correct later than teaching good coding to start with.