Camillia
asked on
Function and Getdate()
I have a table that holds cutoffhours like this:
--
1900-01-01 15:00:00.000
1900-01-01 17:00:00.000--
Below SQL works and I get values like this:
2011-03-01 15:00:00.000
2011-03-01 17:00:00.000
select convert(varchar(10), getdate(), 111) + cutoffhour
from cutoff
---Now, i want to have this in a function and I cant use getdate() in a function. I need to pass getdate() to it. I tried this but get an error to use "convert" . I know i need to use "convert" around cutoffhour ...did that but dont get the "time" part of cutoffhour field
--
1900-01-01 15:00:00.000
1900-01-01 17:00:00.000--
Below SQL works and I get values like this:
2011-03-01 15:00:00.000
2011-03-01 17:00:00.000
select convert(varchar(10), getdate(), 111) + cutoffhour
from cutoff
---Now, i want to have this in a function and I cant use getdate() in a function. I need to pass getdate() to it. I tried this but get an error to use "convert" . I know i need to use "convert" around cutoffhour ...did that but dont get the "time" part of cutoffhour field
Create function dbo.test
(
@CurrentTime datetime
)
RETURNS DATETIME
As
Begin
select @cutoff = convert(varchar(10), @CurrentTime, 111) + cutoffhour --somehow need to use convert with cutoffhour to get 2011-03-01 15:00:00.000 (today's date plus the time in that table)
from cutoff
...
end
What data type is cutoff hour?
Create function dbo.test
RETURNS DATETIME
As
Begin
select @cutoff = convert(varchar(10), GETDATE(), 111) + cutoffhour
from cutoff
...
end
No, you don't need to pass in getdate(), but cutoffhour. Also your convert creates a varchar string, not a datetime. This varchar string then may be implictly converted, as the string format is a valid datetime, but lookup convert in the t-sql helpfile, you'll see that convert(varchar(10),...) converts to varchar(10).
Within the user defined function you're not in the context of the table and cannot adress table fields. But you can make use of any standard/syste T-SQL function within your function.
Then can use this function via
Bye, Olaf.
Within the user defined function you're not in the context of the table and cannot adress table fields. But you can make use of any standard/syste T-SQL function within your function.
Create Function dbo.cutoffstring
(
@cutoffhour varchar(13)
)
RETURNS varchar(23)
As
Begin
return convert(varchar(10), getdate(), 111) + @cutoffhour
End
Then can use this function via
Select dbo.cutoffstring(cutoffhour) from cutoff
Bye, Olaf.
ASKER
You cannot have getdate() in a function.
cutoffhour is datetime...Brandon....
cutoffhour is datetime...Brandon....
ASKER
this is the solution;
convert(char(10), @currentTime, 111) + CONVERT(VARCHAR(8),cutoffh our,108)
convert(char(10), @currentTime, 111) + CONVERT(VARCHAR(8),cutoffh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, if cutoffhour is not varchar, you cannot add it to another varchar. I'd choose 112 style over 111 for the date portion and style 114 over 108 for the time portion. And finally do a explicit conversion via cast.
cast(datetime,convert(char (8), @currenttime, 112) + CONVERT(char(12), cutoffhour,114) as datetime)
Bye, Olaf.
PS: in SQL2008 you can use getdate() in a UDF, as usage of nondeterministi functions is relaxed there. How do I know what version of SQL Server you are using, Farzadw?
cast(datetime,convert(char
Bye, Olaf.
PS: in SQL2008 you can use getdate() in a UDF, as usage of nondeterministi functions is relaxed there. How do I know what version of SQL Server you are using, Farzadw?
This is a full functional example:
Bye, Olaf.
IF OBJECT_ID (N'dbo.overridetime') IS NOT NULL
DROP FUNCTION dbo.overridetime;
GO
CREATE FUNCTION dbo.overridetime(@currenttime datetime, @cutoffhour datetime)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN
return convert(char(8), @currenttime, 112)+' '+convert(char(12), @cutoffhour,114)
END;
GO
IF OBJECT_ID(N'tempdb.dbo.#tmp') IS NULL
BEGIN
create table #tmp (cutoffhour datetime);
insert into #tmp values ('19000101 15:00:00.000');
END;
select dbo.overridetime(getdate(),cutoffhour) as 'mydatetime' from #tmp
Bye, Olaf.