getdate() format problem

7 Apr 2006 11:50 PM

Michael

Hello,

I am just trying to do a simple time stamp. When I do the following SQL
command

SELECT getdate() my book and Microsoft say I should get the format like this:
3/22/2002 6:08:08 AM

Instead when I run this I get the following format:
2006-04-07 18:37:19.823

How do I get it to return the first format?

Thanks,
Michael

8 Apr 2006 12:15 AM

Roy Harvey

GETDATE() returns a datetime datatype, which has no particular display
format, as it is binary in nature. Unless you explicitly convert the
datetime to a character string, SQL Server has nothing to do with how
the date and time are displayed to you. That is a function of the
front end application, such as Query Analyzer (SQL Server 2000) or
Management Studio (SQL Server 2005).

To see what alternatives you have if you convert datetime to a string
explicitly, see the style parameter of the CONVERT function in Books
on Line.

Roy Harvey
Beacon Falls, CT

On Fri, 7 Apr 2006 16:50:02 -0700, Michael
<Mich***@discussions.microsoft.com> wrote:

Show quote

>Hello,
>
>I am just trying to do a simple time stamp. When I do the following SQL
>command
>
>SELECT getdate() my book and Microsoft say I should get the format like this:
> 3/22/2002 6:08:08 AM
>
>Instead when I run this I get the following format:
> 2006-04-07 18:37:19.823
>
>How do I get it to return the first format?
>
>Thanks,
>Michael

8 Apr 2006 2:46 AM

Edgardo Valdez, MCSD, MCDBA

Try this:

declare @date datetime
set @date = getdate()
select convert(varchar(20), @date, 101)+ ' ' +convert(varchar(20), @date, 108)

Show quote

"Roy Harvey" wrote:

> GETDATE() returns a datetime datatype, which has no particular display
> format, as it is binary in nature. Unless you explicitly convert the
> datetime to a character string, SQL Server has nothing to do with how
> the date and time are displayed to you. That is a function of the
> front end application, such as Query Analyzer (SQL Server 2000) or
> Management Studio (SQL Server 2005).
>
> To see what alternatives you have if you convert datetime to a string
> explicitly, see the style parameter of the CONVERT function in Books
> on Line.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 7 Apr 2006 16:50:02 -0700, Michael
> <Mich***@discussions.microsoft.com> wrote:
>
> >Hello,
> >
> >I am just trying to do a simple time stamp. When I do the following SQL
> >command
> >
> >SELECT getdate() my book and Microsoft say I should get the format like this:
> > 3/22/2002 6:08:08 AM
> >
> >Instead when I run this I get the following format:
> > 2006-04-07 18:37:19.823
> >
> >How do I get it to return the first format?
> >
> >Thanks,
> >Michael
>

8 Apr 2006 4:47 PM

Aaron Bertrand [SQL Server MVP]

http://www.aspfaq.com/2464
http://www.aspfaq.com/2460

Show quote

"Michael" <Mich***@discussions.microsoft.com> wrote in message
news:8669696A-88B3-4F33-88E5-42997C68B387@microsoft.com...
> Hello,
>
> I am just trying to do a simple time stamp. When I do the following SQL
> command
>
> SELECT getdate() my book and Microsoft say I should get the format like
> this:
> 3/22/2002 6:08:08 AM
>
> Instead when I run this I get the following format:
> 2006-04-07 18:37:19.823
>
> How do I get it to return the first format?
>
> Thanks,
> Michael