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
>

Get report design from Report Manager

If you cannot find the current Visual Studio sln project file, can you obtain
the report design from the Report Manger to plug back into the designer to
ensure that you start from the most current report verision held on the
server?
Thanks
IanYes. Using Report Manager, select the report you want and from the
Properties tab click on the Edit link to download the rdl file. FYI -
there is a nice and free utility called Reporting Services Scripter by
Jasper Smith that can grab multiple files and download ot transfer them
to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
Matt A|||Thanks Matt I'd completely forgotten this Edit link facility
"reportdude" wrote:
> Yes. Using Report Manager, select the report you want and from the
> Properties tab click on the Edit link to download the rdl file. FYI -
> there is a nice and free utility called Reporting Services Scripter by
> Jasper Smith that can grab multiple files and download ot transfer them
> to another RS - http://www.sqldbatips.com/showarticle.asp?ID=62
> Matt A
>

Get records count from SQL cursor

Hi experts,
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:

> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:

> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:

> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.