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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2008-01-11 : 11:34:08
|
| I'd like to convert a datetime value in a particular field in the database to a date format of my choosing, specificially for retrieval purposes. I don't wish to store the final value into the database. I just would like to retrieve it for use in my (rather stubborn) IDE. Basically, i send a date value from my IDE to the database for storage, in the format of mm/dd/yyyy. Now I know mssql stores the date as the number of seconds from around 1970, something like that. But it displays it as a datetime field. Now the value I store is 01/01/2008. In enterprise manager, it reads 01/01/2008. In sql analyzer, however, it reads 2008-01-01 00:00:00.000. Just thought that was interesting. When I retrive the value in my IDE, it's listed as Jan 1 2008 12:00 AM, I believe. Is there a way I can write a query that formats the datetime field to be mm/dd/yyyy and, if at all possible, maintain it's type as of datetime and not string?Thank you in advance. |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2008-01-11 : 11:46:25
|
| In your SELECT statements, you can use the CONVERT function to convert your DATETIME column into the format you want. To convert it to MM/DD/YYY format, you can use the following:SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]For other date formats, you can refer to Books Online or to the following link:http://www.sql-server-helper.com/tips/date-formats.aspxSQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-01-11 : 12:16:45
|
| Thank you!Additionally, is there a way I can set the column name to, say, Date and still keep it in the format of my choosing, or must I use nested select statements do accomplish this?Also, and this applies to the previous question, can this work for multiple fields of type date in a single query? The "as [MM/DD/YYYY]" throws some complexity into these two questions, no doubt. I looked at the link, but I'll check it again and the other page to see if it can answer the questions.Thanks again. |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2008-01-11 : 14:41:54
|
| Another thought:select [MM/DD/YYYY] as Date from (select convert(varchar(10), Date, 101) as [MM/DD/YYYY] from table_1 where ID = 1)derivedtblI can use the above for one date. But selecting, say, 4 datefields in table_1 is more complicated, I feel. There must be a way... |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-01-11 : 14:51:02
|
| [MM/DD/YYYY] is an alias just change it to Date1 or whatever you want.IEselect convert(varchar(10), GetDate(), 101) as Date, convert(varchar(10), GetDate(), 101) as Date2"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
|
|
|
|
|
Converting Datetime into specified date format