Format a datetime columns output?

[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

[David Portas]

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
--

[Erland Sommarskog]

CK (c_**********@h otmail.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****@sommarsk og.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

[CK]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

CK (c_**********@h otmail.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!

[David Portas]

Erland Sommarskog wrote:

CK (c_**********@h otmail.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
--

[Erland Sommarskog]

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****@sommarsk og.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

[Erland Sommarskog]

CK (c_**********@h otmail.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****@sommarsk og.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