Date/Time formatting

Hi all, I'm hoping this is an easy solution . . .
How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
Am I missing something? I am fairly new to sql server . . .
I would use datepart, but I cannot find a way to display the time like
I have shown.
Your help is appreciated.
Thank you and have a great night,
Ryan<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||Hi David,
First, thanks for posting a reply . . .
I am displaying the data in an asp.net datagrid, so it is actually more
efficient if I format the data on the database server in my sql
statement.
I have looked at the convert function, and I don't see where I can
format the data how I have specified. In ocacle, I would use to_date
and it would be done.
Thanks for trying, but I need a little more guidance then suggesting a
function.
Ryan|||I'm surprised that you think it's more efficient to do formatting in
the database than in ADO. Usually formatting is done client side
precisely to avoid putting that overhead on the database. ADO provides
date formatting through the ToString method for that purpose.
Aside from efficiency, doing it at the client has the advantage of
being user-configurable. Apparently you want to force all your users to
conform to your preference on how they should view dates. Also, if you
return a formatted string in the form you suggested then the user won't
be able to sort on it.
To do it in the database would look something like this:
SELECT CONVERT(CHAR(10),CURRENT_TIMESTAMP,101)+
'
'+RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAM
P,0),8)
David Portas
SQL Server MVP
--|||>> I have looked at the convert function, and I don't see where I can format
SELECT CONVERT( VARCHAR, GETDATE(), 101 ) +
RIGHT( CONVERT( VARCHAR, GETDATE(), 100 ), 8 )
Anith|||Hi again,
David, that is a good point and it would probably be more applicable if
it were a distributed app (it's just an admin tool that we will use).
I just don't see the advantage of pulling back a data set, then looping
through every record and messing with each item individually when I can
have the database engine do all the work, then I just have to display
it.
Anyway, always a learning process. I really appreciate your posts.
Also, a thank you to Anith.
Thanks and have a great day,
Ryan