getdate() format problem

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 thi
s:
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,
MichaelGETDATE() 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
<Michael@.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 th
is:
> 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|||Try this:
declare @.date datetime
set @.date = getdate()
select convert(varchar(20), @.date, 101)+ ' ' +convert(varchar(20), @.date, 10
8)
"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
> <Michael@.discussions.microsoft.com> wrote:
>
>|||http://www.aspfaq.com/2464
http://www.aspfaq.com/2460
"Michael" <Michael@.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

Get the last 100 records

My sql database table gets filled automatically.

Every record gets a current date/time stamp.

I want to select the last 100 records, ordered by the date/time stamp.

The newest records should be the last record in the 100 recordset.

How can I do this?

select id, createdon from
(select top 100 id, createdon
from table
order by createdondesc) a
order by createdon

|||

I get an incorrect syntax error on the last ")":

SELECT

DT, VALUE

FROM

(SELECTTOP 10

DT, VALUE

FROM [CAS SHORT HISTORY]

ORDERBY DTDESC)

|||

Make sure you've given the derived table an alias. Here's a working example:

declare @.table1table (idint identity (1,1), createdondatetime)declare @.startdatedatetimedeclare @.enddatedatetimeset @.startdate ='20060101'set @.enddate ='20070101'while @.startdate < @.enddatebegininsert @.table1values (@.startdate)set @.startdate = @.startdate + 1endselect id, createdonfrom (select top 100 id, createdonfrom @.table1order by createdondesc) aorder by createdon

|||

Hi ca8msm,

You are filling a new table, but I'm having a table [CAS SHORT HISTORY] that is already filled, how should I create an alias for this table?

|||

The above is just an example table. Use the query at the bottom and change the table and field names.

|||

select id, createdonfrom
(select top 100 id, createdon
from @.table1
order by createdondesc) a
order by createdon

What is the "a" doing?

|||

It's creating an alias for the derived table.

|||

Bingo! Found it!

SELECT

*

FROM(SELECTTOP 10

DT

FROM

[CAS SHORT HISTORY] CASALIAS

ORDERBY

DT

DESC)

CASALIAS

ORDERBY

DT

ASC