Mixed Date Formats

[thingssocomplex]

I have received an extract of data from SQL Reporting Services, the date fields in one column are a mixture of UK and USA how do I correct the dates so they all appear dd/mm/yyyy? Many are the USA format so all my months are incorrect when grouping etc..... Any help will be appreciated.

 

[NickHa]

Are you talking about a single column of dates in the SQL source? If so, it's likely that the dates were entered wrongly in the first place. In what way do the dates appear - do you mean "mm/dd/yyyy" and "dd/mm/yyyy"? If so, how do you know that these are reversed for a date like 06/04/2011 - is that 6 April or 4 June? If you provide an example of what you mean, it may help members of this forum to come up with more useful suggestions.

 

[thingssocomplex]

Hi

I have reviewed the data in full all the dates in the one column of date are in american format, but because my regional settings are set for English it reads the dates incorrectly, I need a way of converting american dates to English format so they are read correctly.

 

[NickHa]

From a data perspective, dates are just numbers. The presentation is down to formatting, so presumably there is a Format statement somewhere in the application which is "mm/dd/yyyy"? If so, change that to "dd/mm/yyyy" and that will fix it. I'm not entirely sure if you have contol over this - depends on who is executing the query and how its results get to you. If you control the query string, then the formatting is in your control as well.
If you are getting a string returned like "mm/dd/yyyy" or "Month Day Year" then you can use the DatValue function to get the string back to number form, from which you can format it as you want.
If this doesn't help, give an example of the data you have so I can move from theoretical mode into practical mode.