December 20, 2011 at 10:05 am
Hello All,
I have a requirement like this. I need to change the values format in the date column. I have a table called test_tbl. I have column called run_Date in the test table. I have values in the run_Date as
11.12.11
10.11.11
nov 3,2011
11/25/11
I want to change these values in single format like this
11/12/2011
10/11/2011
11/03/2011
11/25/2011
How to achieve this. Please assist. This is really urgent. Thanks for any help.
December 20, 2011 at 12:16 pm
Formatting should be left to the presentation layer, because it can vary depending on the language or user preferences. Dates should be stored with the DATE datatype, so that the UI knows how to handle it. The DATE datatype is an abstraction and does not contain any formatting.
The fact that your data does not have a consistent format indicates that your "dates" are stored in varchar fields. This is a bad idea for several reasons including the fact that it's almost impossible to validate the data, it's very expensive to perform date arithmetic, and it overrides the settings of the language and user.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 12:18 pm
This is why you should store date data in a datetime column. Since your are on 2008 and your data does not appear to have time values associated the date datatype might be best.
Since you didn't provide ddl and sample data I took the liberty of creating it for you.
;with dates (stringDate)
as
(
select '11.12.11' union all
select '10.11.11' union all
select 'nov 3,2011' union all
select '11/25/11'
)
select CAST(stringDate as datetime)
from dates
I am guessing that you are going to have values that can't (or won't) cast correctly to a date because of bad data. Yous will have to sort those out manually.
It is also possible that you have logical issues. Do you have some data that may be not what you think? 11.12.11 is an example. What exactly is that? In the US that would most likely be Nov 11, 2011 but in other parts of the world it is more likely December 11, 2011.
More than anything I can't stress enough the importance of using a date or datetime to hold this type of data. varchar is simply not correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply