Convert multiple source date format to standard Date Format
I have challenging requirement to convert multiple date types input entered by users to standard date format i.e Input Date Format can be either of DD/MM/YYYY or DDMMYYYY or DDMMYY Example: 08/10/2013 or 08102013 or 081013 And my requirement is to convert this to standard date format, how can I achieve this ? Any help would be appreciated
Truthfully, the front end application / presentation layer should be handling this, and you should be storing the value as a relevant datatype, such as DATE. It sounds like you are storing the value as a varchar/char and trying to apply rules to the values after the fact of capturing. What are you going to do if the values can't be converted to a valid date? Having said that if you needing to convert from strings to real dates, you could use Len() to determine which format it is and datefromparts() to build a real date declare @BadDates table (BadDateValue varchar(10)) insert into @BadDates (BadDateValue) select '08/10/2013' union all select '08102013' union all select '081013' declare @GoodDates table (GoodDateValue date) insert into @GoodDates (GoodDateValue) select case when len(BadDateValue) = 10 then datefromparts(substring(BadDateValue,7,4), substring(BadDateValue,4,2), substring(BadDateValue,1,2)) when len(BadDateValue) = 8 then datefromparts(substring(BadDateValue,5,4), substring(BadDateValue,3,2), substring(BadDateValue,1,2)) when len(BadDateValue) = 6 then datefromparts('20'+substring(BadDateValue,5,2), substring(BadDateValue,3,2), substring(BadDateValue,1,2)) end from @BadDates select * from @GoodDates
Kev Riley is absolutely right and his answer is great. But I would like to reemphasize his statement that this is best handled by the application. The tools for working with dates in most fully featured langauages are far more robust than what is normally available in SQL. But I like options, and instead of using datefromparts, you could use convert (see
http://msdn.microsoft.com/en-us/library/ms187928.aspx) with the proper style options. dd/mm/yyyy for instance is style 103.