| Author |
Topic |
|
kdnichols
Posting Yak Master
232 Posts |
 Posted - 2009-03-24 : 10:00:08
|
Hello All,It's been quite a while since I have posted but I have always appreciated the great help and responses in the past.I have a date format of 2009-01-01 00:00:00.000 and I want the date to be 01-01-2009. I have been learning about the CONVERT Command.The name of the table is ACS_Contacts and the name of the date field is SenDte. Using SQL Server 2005 Studio Management Express what is the correct way to convert the entire column to my desired date format?As always thanks for any and all help! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-24 : 10:13:47
|
| Datetime columns do not have formats. They are all stored the same format internally.The only thing that has a date format is your connection to the database. If you want to change that format, you should look at the SET DATEFORMAT command in SQL Server 2005 Books Online.The database server does have a default date format for new connections, depending on the default language is was installed with, and the default language for each login. Also, your client software may have some default date format that it sets when you make a connection.CODO ERGO SUM |
 |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2009-03-24 : 10:15:05
|
| Maybe this will help:http://www.sql-server-helper.com/tips/date-formats.aspx |
|
|
|
kdnichols
Posting Yak Master
232 Posts |
Posted - 2009-03-24 : 10:51:07
|
| Thanks for the replies. Can someone give me a good example of the code syntax?Do I use the ALTER DATABASE command or is there something better.im1dermike I happened to find that exact same article yesterday while searching. It is good in the fact it has all the commands and formats. |
|
|
|
kdnichols
Posting Yak Master
232 Posts |
Posted - 2009-03-24 : 11:05:05
|
| OK I know this works SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]But it just displays one result. What do I need to do to get all rows to see the same result? |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-03-24 : 11:26:27
|
| Replace the getdate() with your column name SELECT CONVERT(VARCHAR(10), [Column_Name], 101) AS [MM/DD/YYYY] FROM TableName |
|
|
|
kdnichols
Posting Yak Master
232 Posts |
Posted - 2009-03-24 : 11:34:28
|
Hello Neil,Thanks that did work I was very close. Here is my code:SELECT CONVERT(VARCHAR(10),101) AS [MM/DD/YYYY]FROM ACS_Contacts Now how do I get the table to see the date this way every time I open it? What are the correct steps to do this?Thanks,Kurt |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-03-24 : 11:34:46
|
| SELECT CONVERT(VARCHAR(10), yourdate, 101) AS [MM/DD/YYYY] from yourtable This is really a presentation layer issue and should be handled at the application level - IMOTerry-- Procrastinate now! |
|
|
|
kdnichols
Posting Yak Master
232 Posts |
Posted - 2009-03-24 : 12:03:28
|
| Hello Tosscrosby and all,I am converting Access Database tables to SQL Using SSMA that is the issue.Again I can now change the date, how do I use this to reflect it in the SQL Server table every time without having to issue this command?Thanks,Kurt |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-24 : 19:50:20
|
quote:
Originally posted by kdnichols
Hello Tosscrosby and all,I am converting Access Database tables to SQL Using SSMA that is the issue.Again I can now change the date, how do I use this to reflect it in the SQL Server table every time without having to issue this command?Thanks,Kurt
I explained in my last post that datetime columns do not have time formats.CODO ERGO SUM |
|
|
|
|
SQL Server 2005 Date Format Problem