question

shhamihira avatar image
shhamihira asked

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
dateconvertdate-functions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You will also need to establish if these are the only possible input formats for the data - for example would DD/MM/YY be valid? If so, then you will need to adjust slightly the code from @Kev-Riley - for example to search for the position of the '/' characters if they exist in the string, or the code would generate a conversion error. As Kev says, this really looks like a case of the wrong data type being used for the data when it is sent to the database, and I would add a note of caution that in the case of dates sent as strings there is also the potential for misinterpretation (e.g. if MM/DD/YYYY were to be used by a US employee) in the future. Are there perhaps any other date columns that you can use to validate the derived date, to give you confidence that your conversion is correct?
0 Likes 0 ·
Thanks Kevin :)
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.