I declared a @.Start and @.End, and now I want to bring in todays date @.End and 30 days pevious as start... These two commands give me those 2 pieces of data but how do I use(assign) Start and End to these??
SELECT CONVERT(CHAR(10), GETDATE(), 101)
SELECT CONVERT(CHAR(10), GETDATE()-30, 101)DECLARE @.Start char(10), @.End char (10)
SELECT @.Start = CONVERT(CHAR(10), GETDATE(), 101)
SELECT @.End = CONVERT(CHAR(10), GETDATE()-30, 101)
SELECT @.Start, @.End|||how about:
DECLARE @.Start char(10), @.End char (10)
select @.start=getdate()
select @.end = dateadd(m,-1,getdate())
SELECT @.Start as 'start', @.End as 'end'
Showing posts with label getdate. Show all posts
Showing posts with label getdate. Show all posts
Friday, March 23, 2012
getdate() within a function
Im really new to SQL SProcs. I have a function that I wrote that I am trying to compare a date within a record to today's date. The problem is that you cant call getdate from within a function... So, I was thinking that I could create a temp table that had a a date column with a default date of today and select that out. However, I cant find any documentation on how you would create a temp table with a default value, or if this would even work. I dont want to have to pass todays date into the function, nor do I want to have to create a permanent table just to hold this data.
Any help, or other ideas?
Thanks.
You would create a temp table with a default value the same way you would create a non-temp table with a default value, except start the table name with a #.
Why can't you select the date from within the function?
I can't help but feel that you are doing something more basic incorrectly, or with a bad approach to your problem, but without any code, it's hard to tell what you are trying to do.
|||Originally, I was trying to do something like this:
SET @.resDate = (SELECT ...)
IF @.resDate > getdate()
...
That didnt work because you cant call getdate from a function. Then I tried:
CREATE TABLE #tempDate
(
today datetime
DEFAULT(getdate())
)
Which also did not work for the same reason. So... other than passing getdate into the function, is there any other way to do it?
Josh
|||You could also write a view that returns a datetime.
This is no more an issue in SQL 2005 though. So if you are using 2005, you are lucky.
|||what about this function(time1,getdate()). This is what I have ahad to use in the past.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
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
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() to insert into a datetime field
Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:
> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:
> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:
> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:
> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:
> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:
> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:
> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:
> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql
getdate() rounded when runned in a job.
Hi all.
I am confusing on this matter so if anyone recognize this please let me
know.
Simple scenario.
I am using getdate() to fill datetime into a column in a table. All within a
prosedure.
Create a tmp table with datetime column.
Insert getdate()
Insert the temptable into a physical table with a datetime column.
Running the Exec prosedurename for Queryanalyzer I get the the whole
datetime value into the table. '2005-12-22 17:05:00,256'
BUT
Starting a Squeduled job running the same Exec I get only the date and it is
rounded upwards '2005-12-23 00:00:00,000'
Any ideas. I gues this is "by design" or something. I also think this
happens when running the job after 12 in the day.
thanx
geir"when running the job after 12 in the day."
That sounds misterious :-) No, something has to connvert the datetime
value to a less precise value. Does your proc perhaps use an input
paramter which is some kind of casted if you are calling it from a
scheduled job ?
DDL of the procedure and the job would be fine here. Jobs can be
scripted via Enterprise Manager into a sql file.
HTH, jens Suessmeyer.|||Hi Jens.
Sounds like some less precise value and "then I have to make a desission for
the time part and I decide to round up to the next date" issue.
I found out that if I Convert() the datetime as in Convert(varchar(25),
#tmpTable.DateTimeValue) in the moment when I insert into the pysical table
it all got inn. Right date and time.
What I don't understand is the differense between running in a job or in the
QueryAnalyzer.
So, it is solved. I have no ide why :-)
I run exact the same Exec with all hardcode values as parameters.
Exec custMPXrptFilExportSP 13, 3, 1, 17
go
CREATE PROCEDURE custXXXrptFilExportSP
@.KundeID int,
@.AvsAdresseID int,
@.LagerID int,
@.WebDataID int
AS
I am confusing on this matter so if anyone recognize this please let me
know.
Simple scenario.
I am using getdate() to fill datetime into a column in a table. All within a
prosedure.
Create a tmp table with datetime column.
Insert getdate()
Insert the temptable into a physical table with a datetime column.
Running the Exec prosedurename for Queryanalyzer I get the the whole
datetime value into the table. '2005-12-22 17:05:00,256'
BUT
Starting a Squeduled job running the same Exec I get only the date and it is
rounded upwards '2005-12-23 00:00:00,000'
Any ideas. I gues this is "by design" or something. I also think this
happens when running the job after 12 in the day.
thanx
geir"when running the job after 12 in the day."
That sounds misterious :-) No, something has to connvert the datetime
value to a less precise value. Does your proc perhaps use an input
paramter which is some kind of casted if you are calling it from a
scheduled job ?
DDL of the procedure and the job would be fine here. Jobs can be
scripted via Enterprise Manager into a sql file.
HTH, jens Suessmeyer.|||Hi Jens.
Sounds like some less precise value and "then I have to make a desission for
the time part and I decide to round up to the next date" issue.
I found out that if I Convert() the datetime as in Convert(varchar(25),
#tmpTable.DateTimeValue) in the moment when I insert into the pysical table
it all got inn. Right date and time.
What I don't understand is the differense between running in a job or in the
QueryAnalyzer.
So, it is solved. I have no ide why :-)
I run exact the same Exec with all hardcode values as parameters.
Exec custMPXrptFilExportSP 13, 3, 1, 17
go
CREATE PROCEDURE custXXXrptFilExportSP
@.KundeID int,
@.AvsAdresseID int,
@.LagerID int,
@.WebDataID int
AS
GETDATE() Query Giving Me Trouble
Hello all,
I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:
SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo
I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?
Thanks in advance for any help!
Tony
Date comparison's take the full date and time into consideration.
If you're looking to match only on MDY, then try
Code Snippet
datediff(dd, NextDate, GETDATE())=0
|||Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:
Code Snippet
where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1
I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.
( Thanks, Dale; yes, the water is ice cold. It is hot here too. 
LOL
But, you do have a good point Kent.
However, without more info the point might be moot.
datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.
Hope it's ice cold water....it's 95 here today
|||Thanks very much guys for the replies!
DaleJ,
The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?
Thanks again very much for the help!
Tony
|||Hey Tony
datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).
The =0 checks that that difference is 0, meaning that it's the same date
|||Got it. Thanks again very much!GETDATE() Query Giving Me Trouble
Hello all,
I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:
SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo
I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?
Thanks in advance for any help!
Tony
Date comparison's take the full date and time into consideration.
If you're looking to match only on MDY, then try
Code Snippet
datediff(dd, NextDate, GETDATE())=0
|||Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:
Code Snippet
where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1
I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.
( Thanks, Dale; yes, the water is ice cold. It is hot here too.
LOL
But, you do have a good point Kent.
However, without more info the point might be moot.
datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.
Hope it's ice cold water....it's 95 here today
|||Thanks very much guys for the replies!
DaleJ,
The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?
Thanks again very much for the help!
Tony
|||Hey Tony
datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).
The =0 checks that that difference is 0, meaning that it's the same date
|||Got it. Thanks again very much!GETDATE() Query Giving Me Trouble
Hello all,
I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:
SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo
I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?
Thanks in advance for any help!
Tony
Date comparison's take the full date and time into consideration.
If you're looking to match only on MDY, then try
Code Snippet
datediff(dd, NextDate, GETDATE())=0
|||Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:
Code Snippet
where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1
I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.
( Thanks, Dale; yes, the water is ice cold. It is hot here too.
LOL
But, you do have a good point Kent.
However, without more info the point might be moot.
datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.
Hope it's ice cold water....it's 95 here today
|||Thanks very much guys for the replies!
DaleJ,
The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?
Thanks again very much for the help!
Tony
|||Hey Tony
datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).
The =0 checks that that difference is 0, meaning that it's the same date
|||Got it. Thanks again very much!GETDATE() Query Giving Me Trouble
Hello all,
I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:
SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo
I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?
Thanks in advance for any help!
Tony
Date comparison's take the full date and time into consideration.
If you're looking to match only on MDY, then try
Code Snippet
datediff(dd, NextDate, GETDATE())=0
|||Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:
Code Snippet
where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1
I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.
( Thanks, Dale; yes, the water is ice cold. It is hot here too.
LOL
But, you do have a good point Kent.
However, without more info the point might be moot.
datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.
Hope it's ice cold water....it's 95 here today
|||Thanks very much guys for the replies!
DaleJ,
The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?
Thanks again very much for the help!
Tony
|||Hey Tony
datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).
The =0 checks that that difference is 0, meaning that it's the same date
|||Got it. Thanks again very much!sqlgetdate() problem: where is the time taken from ?
Hi,
So you have an application that modifies the computer clock every
second, and now you are asking why:
getdate() does not always reflect you recently updated system time.
:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:
>
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.
I like to stress that is my own speculation of how the internals work.
If my theory is correct, yes, this appears to be a correct conclusion.
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 like to stress that is my own speculation of how the internals work.
>
>
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?
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.
I wont :) Have other things to do as well :)
I'm on 2000 ... right now ...
This is what I was thinking of...
This is what I was afraid of :(
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.
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() parameter in Function.... Not working...
I have a function that has the following two parameters. When I try to
compile I get an error on the GetDate().
If I remove the () from GetDate, it compiles but doesn't return the correct
results.
Example.
ALTER FUNCTION dbo.SiteMaxFileByExt
(
@.Days int = -1,
@.Date datetime = GetDate()
)
RETURNS TABLE
AS
RETURN SELECT TOP 100 PERCENT
tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
MAX(dbo.a_SiphonDetail.FileName) AS FileName
from tmSystem.dbo.vw_SiteExtensionList
where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
Thanks,
Roghttp://www.aspfaq.com/2439
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
> I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>|||Look up "user-defined functions, creating" in BOL.
It explains everything there.
In a nutshell, you cannot use GetDate() inside a UDF.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
>I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
> correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>
compile I get an error on the GetDate().
If I remove the () from GetDate, it compiles but doesn't return the correct
results.
Example.
ALTER FUNCTION dbo.SiteMaxFileByExt
(
@.Days int = -1,
@.Date datetime = GetDate()
)
RETURNS TABLE
AS
RETURN SELECT TOP 100 PERCENT
tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
MAX(dbo.a_SiphonDetail.FileName) AS FileName
from tmSystem.dbo.vw_SiteExtensionList
where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
Thanks,
Roghttp://www.aspfaq.com/2439
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
> I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>|||Look up "user-defined functions, creating" in BOL.
It explains everything there.
In a nutshell, you cannot use GetDate() inside a UDF.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
>I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
> correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>
Getdate() overflowing datetime
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
Chris Honcoop wrote:
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
That sounds strange. Can you use an error output on the destination component to capture the erroring records?
-Jamie
|||
I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.
Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).
|||Chris, have you recently installed any service packs or made other changes?
Thanks
Mark
Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.
FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).
|||FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.
So if this happens to you be on the lookout for this!!!
It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....
Honestly this is quite scary....
|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.
Getdate() overflowing datetime
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
That sounds strange. Can you use an error output on the destination component to capture the erroring records?
-Jamie
|||
I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.
Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).
|||Chris, have you recently installed any service packs or made other changes?
Thanks
Mark
Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.
FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).
|||FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.
So if this happens to you be on the lookout for this!!!
It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....
Honestly this is quite scary....
|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.
Getdate() overflowing datetime
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
I have a weird error that just started showing up. This process has run many times before and just today it started erroring.
The error I get is:
There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.
I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".
Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.
That sounds strange. Can you use an error output on the destination component to capture the erroring records?
-Jamie
|||
I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.
Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).
|||Chris, have you recently installed any service packs or made other changes?
Thanks
Mark
Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.
FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).
|||FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.
So if this happens to you be on the lookout for this!!!
It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....
Honestly this is quite scary....
|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.sql
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
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 User Defined Functions, Parameters in View
1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus
> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.
> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus
> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.
> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields
getdate() in user defined function
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.
Regards,
hmscott
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()
Then reference v_getdate.ThisDate in your function.
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.
Regards,
hmscott
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()
Then reference v_getdate.ThisDate in your function.
Getdate() in UDF column workaround
I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:
>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:
>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql
getdate() in UDF
I am trying to use getdate() function inside of my UDF function.
I am getting an error message about invalid use of getdate() function.
Is there a way to use functions in UDF functions?http://www.aspfaq.com/2439
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23vVy542PFHA.2468@.tk2msftngp13.phx.gbl...
>I am trying to use getdate() function inside of my UDF function.
> I am getting an error message about invalid use of getdate() function.
> Is there a way to use functions in UDF functions?
>
I am getting an error message about invalid use of getdate() function.
Is there a way to use functions in UDF functions?http://www.aspfaq.com/2439
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23vVy542PFHA.2468@.tk2msftngp13.phx.gbl...
>I am trying to use getdate() function inside of my UDF function.
> I am getting an error message about invalid use of getdate() function.
> Is there a way to use functions in UDF functions?
>
Subscribe to:
Posts (Atom)