By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Format a datetime columns output?

P: n/a
CK
Hi All,
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?

Thanks In Advance,
~CK
Jun 7 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
CK wrote:
Hi All,
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?

Thanks In Advance,
~CK


SQL Server has no control over how dates are displayed. You need to fix
the formatting in your client application or development environment.
It's hard to help you because you haven't told us what your client
environment is.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jun 7 '06 #2

P: n/a
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 7 '06 #3

P: n/a
CK

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.


I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View and
then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This is an
internal application but I see why it should be done in code. Thanks!
Jun 7 '06 #4

P: n/a
Erland Sommarskog wrote:
CK (c_**********@hotmail.com) writes:
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?


To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.


You can't actually format dates in a query at all. You can output a
string that looks like a date. I know you know this Erland, but the
difference is worth stating given that the application probably treats
dates and strings very differently.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jun 8 '06 #5

P: n/a
David Portas (RE****************************@acm.org) writes:
You can't actually format dates in a query at all. You can output a
string that looks like a date. I know you know this Erland, but the
difference is worth stating given that the application probably treats
dates and strings very differently.


Well, it depends on what the application will use the output for. If it's
going into a report, it's just another string for the application. If the
application is going do some date logic with it, the result can only be
confusion.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 8 '06 #6

P: n/a
CK (c_**********@hotmail.com) writes:
I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View
and then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This
is an internal application but I see why it should be done in code.


This looks just wrong to me.

If you have a view, and need a condition on it with >=, why one Earth would
you could convert the date to a string? And why would you convert it to a
string that only works sometimes? When working with dates in literals in
SQL Server you should use the format YYYYMMDD, because this format is always
interpreted the same. Most other format depends on language and date format
settings.

If you are passing dates from the application as parameters, you should use
parameterised statements and pass datetime values as such not as strings.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 8 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.