Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2011-06-14 : 10:39:24
|
| How can I convert this: 2011-02-03 00:00:00.000to 3/2/2011? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-14 : 11:30:12
|
If you are trying to convert a date column to string, you could use convert http://msdn.microsoft.com/en-us/library/ms187928.aspx :select convert(varchar(10),yourDateColumn,103) That will always return the months and days with 2 characters (as in 03/02/2011). If you want to strip out the zeros and make 3/2/2011:SELECT CAST(DAY(dateCol) AS VARCHAR(2)) + '/' + CAST(MONTH(dateCol) AS VARCHAR(2)) + '/'+ CAST(YEAR(dateCol) AS VARCHAR(4)) BUT.... Many people on this forum would advise you to do this type of conversion outside of SQL server - for some very good reasons.If you are asking how to change the way SQL Server interprets strings when it is trying to convert them to dates, set the DATEFORMAT appropriately. See here for examples: http://msdn.microsoft.com/en-US/library/ms189491(v=SQL.90).aspx |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-14 : 17:44:02
|
| What is the basic principle of any tiered architecture? Answer: ALL, repeat ALL, data formatting is done in the front end and never in the Database. You also don't seem to know that ANSI Standard SQL uses only one data format -- 'YYYY-MM-DD"; that is why it is used by the DATE data type. You can use CAST (<datetime_expression> AS DATE) to cut off the time part of a DATETIME expression. The better answer is that if you are moving to T-SQL 2008, then alter the tables to use DATE data types where you need them.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
Convert date format