| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2010-04-13 : 10:38:45
|
| Hi, The datetime format of the field system_time in my table is set as dd/mm/yy hh:mm:ss, however when I write a query at the editor at the table and execute it, the date format is changed to yyyy-mm-dd hh:mm:ss.SSS on the results output.Please could you help me to set the date format to dd/mm/yy hh:mm:ss when I execute the query.. Thanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-13 : 10:45:24
|
| what is the datatype of system_time?If it is a DATETIME field then it doesn't have a 'format' it only has formatting when you try and select it as a varchar(). How are you querying the database?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-13 : 10:51:17
|
if you are using a Query window to execute your query, that is how the query window present the date and time to you.In your application, you determine how you want to present the date or date and time.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-13 : 10:57:14
|
| There is not format setting for datetime datatypes. Dates and times are formatted by the client, not the server. If you do want them to be displayed in a specific format at the server, then you can use the CONVERT function to do that. Here's an example using GETDATE() You can replace GETDATE() with your column name.SELECT CONVERT(VARCHAR, GETDATE(), 3) + ' ' + CONVERT(VARCHAR, GETDATE(), 8)The first call to CONVERT returns the date, and the second returns the time. There are various ways to format a DATETIME value with CONVERT, but none of them give the exact format you're after. That's why I've used two calls. Have a look at BOL on CONVERT for more info.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2010-04-13 : 10:59:08
|
| The data type is Datetime. I query the database by the select syntax shown below;select * from textsql.TblCollectionswhere prac_no = 20750So in the table field system_time is set to 10/07/01 00:00:00, however when I run the query I receive 2001-07-10 00:00:00.000 for the system_time. Is there any way I can change the format to be identical to the one stored in the table when I execute the query.. Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-13 : 11:02:57
|
"So in the table field system_time is set to 10/07/01 00:00:00"Not quite .... it is set to an internal representation of that date. It is retrieved (and sent to your application) as a DATETIME object with no implicit formatting. If you view it with SSMS (say) that defaults to showing it as "2001-07-10 00:00:00.000", if you retrieve that value into your application then your application can display it in whatever format it likes. As another example if you Cut & Paste the data from SSMS into Excel then you can format it in Excel however you like  You should not, normally, format the date at SQL Server send, you should do that in the client application. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-13 : 11:04:27
|
| The column system_time is not stored as 10/07/01 00:00:00 or in fact any other format. Internally it is just a number. It's only when you present the information that you are seeing a format.You can do as 'DBA in the making' suggested when doing a SELECT to format the column any way you like.If you are selecting from your application then it's better not to try and format in the database. Just store the column in an appropriate date time container for whatever application language you are using.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2010-04-13 : 11:07:41
|
| Therefore, it is not possible to have an identical datetime format when I am in the query editor mode as to the format I see on the open table level? |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-13 : 11:27:27
|
quote:
Originally posted by dr223
Therefore, it is not possible to have an identical datetime format when I am in the query editor mode as to the format I see on the open table level?
Not unless you specifically convert it to another format.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-13 : 11:35:24
|
| AFAIK SSMS always displays dates in "yyyy-mm-dd hh:mm:ss.sss" format, and there isn't any configuration option to change that.I suspect that the Open Table is using the default date format as set up on your Windows machine (so you might be able to modify that to be the same as the Query format). |
|
|
|
|
Datetime format