Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
On SSIS,how do I convert from staging table(nvarchar) to Loading table(date & float).Thank you

What I have tried:

Data conversion from nvarchar to date
Posted
Updated 28-Apr-22 4:52am
Comments
Patrice T 28-Apr-22 10:22am    
To bad there is no documentation and Google don't work in your place.

1 solution

Basically, don't.
If you are storing date and / or float information as NVARCHAR, then your data design is flawed, and you have opened up a can of worms for yourself that will probably take a fair amount of human effort to fix.

The problem is that not all people use the same date format (or even the same date), or the same number format: in the UK and USA, 77.888 is seventy seven and 888 thousandths. In Germany, that would be seventy seven thousand eight hundred and eighty eight.
Dates are even worse: 01/02/03 can be the 1st Feb 2003, 2nd Jan 2003, or 3rd Feb 2001 - and once they arrive at the DB there is no context available any more to give any clue as to which.

Add to that that some people will happily enter "today" for the date if given a chance and your DB can very quickly be filled with bad data that may or may not be translatable to a valid-looking-but-incorrect value.

By the time you come along and try to convert it to usable data it's too late, and your app can't cope with what it gets, or trust it anyway.

You need to change your DB (and the app that fills it) to store information in relevant fields: dates in DATETIME, DATETIME2, or DATE columns, numbers in relevant numeric fields, then validate and convert dates to internal form for storage while it has access to the user preferences. Anything else is a poor stopgap that will cost you huge amounts of time to fix later (where that is even possible)
 
Share this answer
 
Comments
Samuel Babajide 28-Apr-22 11:16am    
Thanks a bunch
OriginalGriff 28-Apr-22 11:33am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900