getdate() not returning a value

I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

For example,

select * from polldates
where (pollbegindate >= getdate() and pollenddate <= getdate())

This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

Any help would be greatly appreciated!I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP|||If you are sure that get date is returning a correct value but I am not getting anything back from my query can you suggest how to improve the query?

For example, the begin date is 9/5/04 and the end date is 9/11/04.

Thanks!|||Is it safe to assume pollbegindate and pollenddate are datetime datatypes in the table? Please post the enitre proc. There may be another problem.|||I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP

Well that was CERTAINLY helpful...

Dude

Do SELECT GetDate()...what do you see?

Ahh microseconds...

USE DATEDIFF

But the logic doesn't make sense...

You want all begin dates that are today and greater but all end dates that are less that or equal today...which means...

And day where the start and end are equal and it's TODAY

Johhny...tell him what he's won.....|||Maybe we all need to read. Now I feel like an idiot (well, I almost always feel like an idiot, but that's another matter).SELECT *
FROM polldates
WHERE pollbegindate <= getdate()
AND pollenddate >= getdate()The previous code was looking for rows where the begindate was greater than the enddate!

-PatP|||Even with the screwed up logic why does it return records everyday but Sunday?|||Me no know.

Without seeing the real query and the underlying data, I can offer a gazillion guesses, but no hard facts.

-PatP

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' +
convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate
()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate())
, 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (T
his happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happe
ning?
Thanks,
C.Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

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

Get User-Input into a Stored Proc

Hi,
Is there any way to accomplish this (SQL Server 2000):
Client-App sends an Update to an SP.
The SP has to perform various actions, amongst one setting a FK.
Normally there will only be One FK possible per Update.
BUT
It can happens there are multiple FK's possible for One Update.
Under that condition the possible FK's should be sent back to the App where
the User can select One FK.
Once that FK is returned to the SP, the Procedure can continue it's actions.
I fear this is science fiction though I'd like to be sure :-)
TIA,
MichaelMichael,
A stored procedure has no way to request further input from the user. You
can, of course, write two stored procedures (1) figure out if all is well
and (2) do the work, then design your app to use the procedures.
RLF
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||You can of course do all of this - but not in a stored proc. Why would that
matter? Stored procs are not for UI code.
David Portas
SQL Server MVP
--
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||Hi David & Russel,
Thanks for your replies.
The reason I would like to implement this is that various Bit, DateTime & FK
fields have to be set accross various tables depending on certain Updates on
another table.
On itself this is pretty straight foreward, but in the App this procedure
can be started on many forms under various ways and conditions.
It is the Undoing of this operation that is tadious in the App. (so many
variations). It makes it easy to break the logic.
Having it all done by an Update-Trigger on that table, causing it to launch
various sp's, makes it all solid.
The only caveat is that there * can * be more then One FK and it's
impossoble for non human-logic to determine which to use.
Thus I was "hoping" there would be any means to have a user-interaction on
this level.
I think I will have to come up with an alternative.
Any way: thanks for the input guys!
Regads,
Michael
"David Portas" wrote:

> You can of course do all of this - but not in a stored proc. Why would tha
t
> matter? Stored procs are not for UI code.
> --
> David Portas
> SQL Server MVP
> --
> "Michael Maes" <michael.maes@.community.nospam> wrote in message
> news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
>
>|||> The only caveat is that there * can * be more then One FK and it's
> impossoble for non human-logic to determine which to use.
I've no idea what this means. The foreign keys on a table are fixed
unless you are executing DDL in your update. So how can there be any
doubt about which they are?
David Portas
SQL Server MVP
--|||I think I have expressed myself badly.
it's all about the Value of the FK to save.
To put it in a simplified example:
A workorder can consist of various tasks.
Each task is a certain day performed by a certain technician (the FK)
In another table (installation - statistics) you can see what date the last
visit was by which technician for what type of job, ...
Normally an orders' childrececords (tasks) is always performed by the same
technician. But sometimes more technicians are assigned to an order (each
having his own taskrow).
Since the Technician FK only holds one Value, the user has to decide which
technician to assign for "the last visit" because it's important for
follow-up & support to know who was the "most important" technician. It's
impossible for 'Code' to know which one to choose.
Hence the User-Input.
I hope this clarifies a bit my 'case'.
Regards,
Michael
"David Portas" wrote:

> I've no idea what this means. The foreign keys on a table are fixed
> unless you are executing DDL in your update. So how can there be any
> doubt about which they are?
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Michael,
Is it possible for you to provide a simplified table schema with sample
data to clarify it more? I am afriad one of the most possible resolution is
redesign the table structure or application structure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
Thanks for your reply.
For the moment I have worked around the issue, so I guess the 'Case is
closed' :-)
Thanks,
Michael
"Michael Cheng [MSFT]" wrote:

> Hi Michael,
> Is it possible for you to provide a simplified table schema with sample
> data to clarify it more? I am afriad one of the most possible resolution i
s
> redesign the table structure or application structure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Michael,
You are welcome and thanks for the update.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

get the row counts for each day going back to 6 months (was "query help")

I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.

With this proc i can get one day's row couts.. i need to loop through for all dates.

Please can someone get me the code for this.

create proc p_rowcounts

@.Date1 datetime,
@.Date2 datetime

SELECT
count (*) as 'Number of Rows', @.Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @.Date1 and ModifyTime < @.Date2

thanks for the help.I'd do it as:CREATE PROC p_rowcounts
@.Date1 datetime = NULL
, @.Date2 datetime = NULL
AS

IF @.Date1 IS NULL SET @.Date1 = GetDate()
IF @.Date2 IS NULL SET @.Date2 = DateAdd(month, -6, Convert(CHAR(10), @.Date1, 121))

SELECT
Count (*) AS 'Number of Rows'
, Convert(DATETIME, Convert(CHAR(10), ModifyTime, 121)) AS Date
FROM Table1 (nolock)
WHERE ModifyTime BETWEEN @.Date2 AND @.Date1
GROUP BY Convert(CHAR(10), ModifyTime, 121)

RETURN-PatP|||pat, i think sskris wants one count per date in the range|||That query ought to give one count per day in the range. I think you're hinting that you'd like to see rows with zeros for a count for days with no data, which I see as wasteful and poor practice.

If you have code that relies on zeros, you can certainly go to added trouble to make the zeros appear, but in my mind you'd be much better off to fix the code instead of writing SQL to cater to the problems in it.

-PatP

get return value from stored proc

How can I get the return value from stored procedure?
Basically, if employee already exists in the following sp, I would like to get the ReturnCode -1 in VB 6 app and display "employee already exists" message. Or, I would like to raise an error in sp which can be displayed in VB client app. How can I do this? Thanks.

hr_addNewEmployee:

DECLARE @.lReturnCode INT
IF EXISTS ( SELECT e.EmployeeID
FROM Employee e
WHERE
e.EmployeeID = @.NewEmployeeID)
BEGIN
SELECT @.lReturnCode = -1
RETURN @.lReturnCode
END

ELSE
...
...
...
RETURN @.lReturnCode

Pretty easy:

declare @.returnValue int
execute @.returnValue = procedureName
select @.returnValue

there is also a way to get that info from the ADO.NET, but you would want to go to the ADO.NET forums for that info

To raise an error just add:

Raiserror ('your error here',16,1)

That is what I would probably do, though for your error, if you are inserting a single employee. just insert the row and let the constraint get it. Same amount of work to check existence but when the work is done, you just have to add the row to the physical structures, rather then do it again.

The duplicate row error will tell you that you have a duplicate (it will always be the same error number) so you can catch it on the client side, or in 2005 you can use a try...catch and look at the ERROR_NUMBER() function and get that value.|||

Yes you can...

Code Snippet

Dim oconn As New ADODB.Connection
oconn.ConnectionString = "{Your Connection STRING}"
oconn.Open

Dim ocmd As New ADODB.Command
Set ocmd.ActiveConnection = oconn
ocmd.CommandType = adCmdStoredProc
ocmd.CommandText = "dbo.testReturn"

Dim param As New ADODB.Parameter
param.Direction = adParamReturnValue
param.Type = adInteger
ocmd.Parameters.Append param

ocmd.Execute

MsgBox param.Value

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)