Getting a 404/401 on a long running report

Greetings.
I have a report that I am running through ASP.Net with the rs.render method.
When I have a longer date range, the execution time of the query gets longer
and I start to get funky errors. Namely, at around 3 minutes, I get
prompted for network credentials by the standard IE prompt. It doesn't
accept what they are, and after a few times, it takes me to a 404 saying that
the page can't be found. If I try to cancel the prompt, I get a 401 that I
don't have access. I'm not sure what else to look at here. This report has
worked fine in the past, and there are no problems if I just scale back the
date range.
I have the render make a pdf file in a virtual directory on my web server
and then my postback page has a client script inserted to open a window
showing that pdf file.
Anyone have any ideas?
Dale.I fixed it.
It turns out that the HTTP connection timeout settings needed to be
increased a little bit to allow the report to complete. The default of 120
seconds had worked fine in IIS 5 on Windows 2000 Server, but the same value
in IIS 6 on Windows 2003 Server didn't fare too well.
Also, I wound up re-writing the stored proc for the report. The original
author of it had cursors and correlated subqueries doing things they were
never intended to do. Execution time is now 33s instead of 10m15s.
:-)
"Dale M." wrote:
> Greetings.
> I have a report that I am running through ASP.Net with the rs.render method.
> When I have a longer date range, the execution time of the query gets longer
> and I start to get funky errors. Namely, at around 3 minutes, I get
> prompted for network credentials by the standard IE prompt. It doesn't
> accept what they are, and after a few times, it takes me to a 404 saying that
> the page can't be found. If I try to cancel the prompt, I get a 401 that I
> don't have access. I'm not sure what else to look at here. This report has
> worked fine in the past, and there are no problems if I just scale back the
> date range.
> I have the render make a pdf file in a virtual directory on my web server
> and then my postback page has a client script inserted to open a window
> showing that pdf file.
> Anyone have any ideas?
> Dale.

Getting 404 when trying to run http://localhost/reports

I have a several reports I want to load to the Report Manager I have run the report configuration manager and setup everything. Every time I try to deploy to the I keep getting a 404 http error. I also added several reports fro another machine to this server and I reset the config file but the report cant find the dataset ??

have you successfully deployed your report server? (in vs, from the solution explorer, right click your project and select 'Deploy')

Getdate() with no time associated

Is there a command that will let me set getdate() a in a smalldatetime field so that the there is no time associated with it?

For example, I have a table that I want to load the date a user does an action. If I use getdate() I'll get a value such as 5/25/2006 08:26:56.340, whereas I would just like a value 5/25/2006.

I can work it out by doing the following: select (datename(month,getdate())+'-'+datename(day,getdate())+'-'
+datename(year,getdate()))

However it seems to me that there should be a simpler way.well, i dunno if it's simpler, but this is a lot more efficient --

dateadd(d,datediff(d,0,getdate()),0)|||Towards the bottom of this article is an explanation on the why and how :)

EDIT - how about I post the article link eh?
http://www.sql-server-performance.com/fk_datetime.asp|||That does seem more efficient (I knew there had to be a better approach). And thanks for the link to the article.|||fabulous link, pootle, thanks

GETDATE() with a user defined function

Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO|||Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO

Pass GetDate() as a function argument.

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

-PatP|||Pass GetDate() as a function argument.
-PatP

Did I already say that?

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

oooo scary...

Did you see Hendersons work for Arrays in SQL Server?

And you're right...I chickened out...was going to build them...but I figured why bother...a tables an array, and with the table variable is was even easier...|||If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree ;) Although..."that which does not kill us..." ;)|||Thousands of dollars ?!?! Was she worth it?

-PatP|||*LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!

I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!

You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!

-PatP|||Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it ;)

Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!

(hey, how's THAT for coming back on-topic ;) )|||Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it

Did you get a room with the view of the lake?

This is good Yak Corral stuff...

And Pat do you use debugger or not?|||On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.

The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.

-PatP

getdate() problem: where is the time taken from ?

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.

This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

So you have an application that modifies the computer clock every
second, and now you are asking why:

Quote:

Originally Posted by

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :
>
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First, thanks Erland for the answer!

Quote:

Originally Posted by

So you have an application that modifies the computer clock every
second, and now you are asking why:

:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:

Quote:

Originally Posted by

Quote:

Originally Posted by

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

>
getdate() does not always reflect you recently updated system time.
>
I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.
>
One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.

I like to stress that is my own speculation of how the internals work.

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.

Quote:

Originally Posted by

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Quote:

Originally Posted by

The getdate() is not taking the current system time, only the buffered
SQLServer system time.

>
I like to stress that is my own speculation of how the internals work.
>

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

>
If my theory is correct, yes, this appears to be a correct conclusion.

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

I wont :) Have other things to do as well :)

Quote:

Originally Posted by

If you are on SQL 2005, you could write a CLR function which retrieves

I'm on 2000 ... right now ...

Quote:

Originally Posted by

If you are on SQL 2000, you would have to write an extended stored

This is what I was thinking of...

Quote:

Originally Posted by

procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

This is what I was afraid of :(

Quote:

Originally Posted by

But getdate() seems dead in the water when you are living in the fast lane
like you do.

I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

GetDate() in Constraint using user's system time, Stored Proc using Server's.

All,

I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().

I performed the following test:

1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.

2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.

*note: These servers are on separate domains and therefore could have different system times.

This causes my select stored procedure to NOT return the values I just entered.

Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?

If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||

Todd:

Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.

Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.

sql

getdate() function

im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql

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

GETDATE() and the local time zone

I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?You'll have to explain, because getdate() returns the system time where it is executed, time zone included. So.......|||Hmm.. that set me thinking about another date issue we have. We run in a citrix environment and I have noticed odd date displays before. This function behaves differently depending on whether I use RDP or ICA. I tested it by logging in under both types of session and creating an expression with GETDATE() in it and then evaluating it. The results were correct for RDP but not for ICA - so it looks like the problem is outside SSIS.

GetDate()

G'day,
I want to display the current time minus 5 minutes. say
2004-03-29 11:43:49.123 to be 2004-03-29 11:38:49.123.

Is there any Equivalent to the DB2's CURRENT TIMESTAMP - 5 MINUTES which will display the current day say 2004-03-29 11:43:49.123 as
2004-03-29 11:38:49.123

cheers
Melbselect dateadd(mi, -5, getdate())

but: "Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds)." from BOL 'datetime and smalldatetime'.|||Thanks Kaiowas

cheers
Melb

getdate - only want the date

when I am doing a query I want to display just the date.
when I use the getdate function it returns the date and
time from each of the fields...is there a way to just
display the date from the fields and leave out the time?Use CONVERT or CAST. You can find some examples in books online.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||You can use following to display only date.
select convert(char(11),getdate())
If you want to display in different format , then extract
dd, mm, yy from getdate and concatinate it.
Suryakant
>--Original Message--
>when I am doing a query I want to display just the date.
>when I use the getdate function it returns the date and
>time from each of the fields...is there a way to just
>display the date from the fields and leave out the time?
>.
>|||The only way is to convert it to a string. There are several formats so you
should check out CONVERT() in BooksOnLine for the one your after.
--
Andrew J. Kelly
SQL Server MVP
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||worked fine thank you

Getdate

I believe set @.today=getdate will give me the current time.
How can I get the system date without the time?GETDATE() gives you the current date & time. There are no separate date and
time datatypes in SQL Server. You can use CONVERT() to get a string that
represents the Date portion. Something like
SELECT CONVERT(VARCHAR(10),GETDATE(),101)
Check out CONVERT in BOL for more details. Also have a look here:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||DECLARE @.today DATETIME
SET @.today = GETDATE()
SELECT @.today
SET @.today = DATEADD(day,DATEDIFF(day,0,@.today), 0) --Strip off the time
part
SELECT @.today
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Hi
GETDATE() / CURRENT_TIMESTAMP will return the current date and time.
CONVERT(CHAR(8), GETDATE, 112) will return the date.
CONVERT(CHAR(12), GETDATE, 114) will return the time.
Regards
Mike
"Arne" wrote:

> I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Arne,
If you want the date in ISO standard format, you can use the
following:
declare @.tst char(8)
set @.tst = convert(char(8),getdate(),112)
print @.tst
Look up the CONVERT function in BOL.
On Mon, 21 Feb 2005 06:35:13 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:

>I believe set @.today=getdate will give me the current time.
>How can I get the system date without the time?

GetDate

I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

Get Today's Records

Hello, I have a query that needs to run daily and only collect records for
that day's activity. The table has a date/time field called TranDateSold.
Could someone please advise the proper syntax for the following logic:
SELECT * FROM tablename
WHERE TranDateSold (is today only)
I experimented with GETDATE() but it seemed to want to match today's date
and time to the hour and minute. Any ideas would be most appreciated.Hi,
Strip the time portion from the date returned by getdate() function before
comparison. Something like this:
select *
from tablename
where trandatesold = convert(vachar(8), getdate(), 112)
This assumes that the time is already zero-ed in trandatesold column.
hth,
Dean
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0BA6C334-70D4-4637-8528-1B838763F5DD@.microsoft.com...
> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
> Could someone please advise the proper syntax for the following logic:
> SELECT * FROM tablename
> WHERE TranDateSold (is today only)
> I experimented with GETDATE() but it seemed to want to match today's date
> and time to the hour and minute. Any ideas would be most appreciated.|||Try this
SELECT * FROM tablename
WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
If there can be nothing in the future:
DECLARE @.dt SMALLDATETIME
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt;
If there may be future-dated rows:
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt
AND TranDateSold < @.dt + 1;
A|||Oops, I goofed the = should be >=|||This will only work if TranDateSold is intentionally stored with no time
value. Otherwise you will be asking for rows where '2006-04-21 13:26' =
'2006-04-21 00:00';
If you have to do the convert on the left-hand side to get rid of the time
component, you've just wiped out any chance of using an index.
http://www.aspfaq.com/2280
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649232.038005.231380@.g10g2000cwb.googlegroups.com...
> Try this
> SELECT * FROM tablename
> WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks to everyone for your posts. The convert function is what I needed fo
r
my vendor, and the DECLARE stmt gave me all records after midnight today.
Have a nice wend, Pancho.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If there can be nothing in the future:
> DECLARE @.dt SMALLDATETIME
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt;
> If there may be future-dated rows:
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt
> AND TranDateSold < @.dt + 1;
> A
>
>

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

get timezone specific date

Hello,

I am struggling with one algo i.e. I want to retrieve the date according to time zone

I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...

now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone

can any one tell me, how will I able to achieve this task.

for example:

it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006

Note: Server date is set to GMT (System date)

regards,

Anas

i HAVE DEVISED THE FOLLWING ALGO

IF @.OFFSET - DATEPART(HH,GETDATE())>=0

SET @.SYSDATE = GETDATE()

ELSE

SET @.SYSDATE = GETDATE()-1

PRINT @.CDATE

WHERE @.OFFSET IS NO. OF HOURS DIFFERENCE FROM GMT

ANY BETTER SOLUTIONS ARE WELCOME

REGARDS,

ANAS

|||

Where is the problem? If you have a TimeZone table you could use a query like this

select dateadd(hour, TimeZone.GMTDiff, getdate())

From TimeZone

Where Zone = 'US'

|||

THANX FOR YOUR BETTER SOLUTION

I DIDNT TRY THIS

I WAS USING DATEDIFF BUT COULDNT SOLVE THE PROB.

THANKS AGAIN

get time of querry?

is there a way to get the time that it takes a querry to run like down to the millsecondon 2005 you can use this timer class mladen wrote:

http://weblogs.sqlteam.com/mladenp/archive/2006/12/02/39124.aspx

otherwise you can use the datediff, etc functions in sql server for timing. This won't give you ms accuracy though, since the clock in sql server only has 3ms resolution. perhaps that's enough for you though.|||is there a way to get the time that it takes a querry to run like down to the millsecond

Use SQL Profiler. The duration columns records the time it takes to run a query down to the ms.

Or use SET STATISTICS TIME ON

Or are you trying to access this info programmatically?

Regards,

hmscott

Get Time of Linked Server

Hello,
I have a processes which access a SQL Server 2000 in Brazil, Japan,
China.. and a few other places. I would like to return the localtime
of the linked server.
I cannot find anyway to do this, and can't find a setting in any of
the tables which shows the current timezone.
Any help would be great.
AllanSQL server does not care about Time Zones and does not store it internally.
The Windows Host gives it the time and that all what it wants and needs.
You can get the time on the remote servers by using
SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT CURRENT_TIMESTAMP')
If you need to know it's timezone, you need to access the registry on the
remote machine as Windows stores it here.
Regards
Mike
"Allan Martin" wrote:

> Hello,
> I have a processes which access a SQL Server 2000 in Brazil, Japan,
> China.. and a few other places. I would like to return the localtime
> of the linked server.
> I cannot find anyway to do this, and can't find a setting in any of
> the tables which shows the current timezone.
> Any help would be great.
> Allan
>