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,
~CKCK 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/...US,SQL.90).aspx
--|||CK (c_kettenbach@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DBEF917A66FYazorman@.127.0.0.1...
> CK (c_kettenbach@.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!|||Erland Sommarskog wrote:
> CK (c_kettenbach@.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/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||CK (c_kettenbach@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment