Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am importing data from another sources, which has multiple columns but I am facing problem in date column like I have multiple format in this column like:

dd/mm/yyyy
mm/dd/yyyy
mmm-yyyy

Can you guide me how can accommodate these format during migrate data (using cursor to transfer data from temporary table to destination table).

How can i convert mmm-yyyy date format to dd/mm/yyyy format

share|improve this question
2  
You have different format in a single column? How do you know if 01/04/2011 means April 1 or January 4 ? – bernd_k Jun 23 '11 at 13:10
that's the problem – Xulfee Jun 23 '11 at 13:24
4  
For dates like that there is no solution. – bernd_k Jun 23 '11 at 13:29
@Tony as stated in the question the column is a date column. Even if you don't know the difference,SQL server knows the difference. – StanleyJohns Jun 24 '11 at 0:22
@Stan - I understood the question to be concerning importing data in to a date field. If the OP already has the data in 'date' form there would be no need to ask about accommodating the different formats mentioned in the question. – Tony Jun 24 '11 at 7:06
show 2 more comments

1 Answer

up vote 5 down vote

There is no way to distinguish the meaning of 01/02/2011 and 02/01/2011 if you have this values mixed in the same column. It is that simple.

share|improve this answer
+1 The only time you will be able to tell the difference is when the value of the number in the month position is greater than 12 but that's not going to help in all the other cases. – Tony Jun 23 '11 at 21:08
1  
That answer assumes that the only info you have is the string representing the date. if there is something else on the row which can infer the type of formatting used then maybe it can have some smarts... – Andrew Bickerton Jun 24 '11 at 10:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.