GetDate() Does not Return Milliseconds ?

Ladies / Gentlemen
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:

> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>