Solved

Convert Year string to date

Posted on 2012-12-28
9
323 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:gwarcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 38727605
An expression like this should allow to get a proper date all time, as long as the date delimiters are not mixed up (dots, slashes etc. mixed):
  case when birthdate like '%/%/%' then birthdate else '01/01/'+birthdate end
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 38727627
I'd allow for dates of mm-dd-yyyy also, like so:

CASE WHEN LEN(birthdate) = 4 THEN '01/01/' + birthdate ELSE birthdate END

Btw, if you're going to store a date as varchar, the best format is 'YYYYMMDD' -- NO delimiters.  That allows sorting and guarantees that SQL can always correctly interpret the date, regardless of any SQL settings.  [It also saves a little space, but that's not nearly as significant.]
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38727643
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38727678
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.
0
 

Author Closing Comment

by:gwarcher
ID: 38727744
Thank you, It worked as a case statement.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38727951
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?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38728062
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38728178
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38728357
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.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question