 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Ant
Guest
|
 Posted: Thu Oct 19, 2006 6:03 pm Post subject: SQL Server U D Function |
 |
|
Hi,
Using SQL Server................
I am trying to creat a function that will take a date in a return the start
of the day for that date.
I have managed to do it it returning a table, but I dont want to have to use
a selct statement to get the resultt.
I'd like to be able to do
Select OrderId, StartOfDay( OrderDate ) from Order
So, what's wrong with the SQL below
ALTER FUNCTION StartOfDay
(@DateIn DateTime)
RETURNS @ResultDate DateTime
as
begin
Declare @MyMins Integer;
Declare @MyHours Integer;
Declare @MySeconds Integer;
Declare @TempDateSS DateTime;
Declare @TempDateMI DateTime;
Set @MyHours = DatePart( hh, @DateIn ) * -1;
Set @MyMins = DatePart( mi, @DateIn ) * -1;
Set @MySeconds = DatePart( ss, @DateIn ) * -1;
Set @TempDateSS = DateAdd( ss, @MySeconds, @DateIn );
Set @TempDateMI = DateAdd( mi, @MyMins, @TempDateSS );
Set @ResultDate = DateAdd( hh, @MyHours, @TempDateMI );
RETURN;
end |
|
| Back to top |
|
 |
Kevin Frevert
Guest
|
| Posted: Thu Oct 19, 2006 6:16 pm Post subject: Re: SQL Server U D Function |
|
|
Ant,
This is what we use...
CREATE FUNCTION udf_StartOfTheDay (@AValue DateTime)
RETURNS DateTime AS
BEGIN
SET @AValue = CONVERT(varchar,@AValue,101) + ' 00:00:00:000'
RETURN(@AValue)
END
Good luck,
krf
"Ant" <adcruze (AT) tickets_NOSPAM (DOT) com> wrote in message
news:4537777f$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Hi,
Using SQL Server................
I am trying to creat a function that will take a date in a return the
start of the day for that date. |
|
|
| Back to top |
|
|
Steve Troxell
Guest
|
| Posted: Thu Oct 19, 2006 6:20 pm Post subject: Re: SQL Server U D Function |
|
|
Ant wrote:
| Quote: | Hi,
Using SQL Server................
I am trying to creat a function that will take a date in a return the start
of the day for that date.
So, what's wrong with the SQL below
|
You're missing milliseconds for one.
Looks like all you are doing is stripping the time from a date/time. Try
this instead:
select convert(datetime, convert(int, getdate()))
(change getdate() to a variable for your function)
Steve Troxell |
|
| Back to top |
|
|
Vitali Kalinin
Guest
|
| Posted: Thu Oct 19, 2006 6:29 pm Post subject: Re: SQL Server U D Function |
|
|
| Quote: | So, what's wrong with the SQL below
You mean except code snippet you have provided failed to run in QA and you |
don't reference yours function as dbo.StartOfDay? :-)
Anyway this one works fine for me:
ALTER FUNCTION StartOfDay(@DateIn DateTime)
RETURNS DateTime
as
begin
return cast(floor(cast(@DateIn as float)) as datetime)
end
select OrderId, dbo.StartOfDay(GetDate())
from Order |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
