Funny DateTime Issues

Hello this is weird when I run this on a Friday
SELECT DATENAME(dw,5) --> returns 'Saturday'
Select DATEPART(dw,GETDATE()) --> returns 5
Can anyone explain why this would occur?Check your @.@.DATEFIRST value. It might be set to 1 ( Monday ). You can
change it using SET DATEFIRST statement.
Anith|||I think the problem in the first query is that sql see the number that you
pass as the day of the month or a Julian date. The value of the second
parameter to DATENAME should be a valid date. If you run SELECT DATENAME(dw,
GETDATE()) then you will get "Friday". The second query you have returns the
wday number. For example Sunday = 1...Saturday = 7. That can be changed
by SET DATEFIRST. But by default 5 is the wday number for Friday.
Hope that helps
Tim
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ewb3BkG7FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Check your @.@.DATEFIRST value. It might be set to 1 ( Monday ). You can
> change it using SET DATEFIRST statement.
> --
> Anith
>|||The second parameter for DATENAME is a date - this call is getting the
day of the w for 1900-01-06, which is a Saturday. The implicit
conversion is based on 0=1900-01-01, ergo 5=1900-01-06.
As Anith has said, DATEPART is dependent on DATEFIRST, which is probably
set to Monday in your system.
yurps wrote:
> Hello this is weird when I run this on a Friday
> SELECT DATENAME(dw,5) --> returns 'Saturday'
> Select DATEPART(dw,GETDATE()) --> returns 5
> Can anyone explain why this would occur?
>|||Ok, if this is the case, try this out and explain why it works
set datefirst 1
declare @.bd datetime
select @.bd = '2005-12-01 00:00:00';
with dd (FullDateAlternateKey,
DayNumberOfW,HourNumber,EnglishDayNam
eOfW)
as
(
select @.bd,datepart(dw,@.bd),datepart(hh,@.bd),da
tename(dw,@.bd)
union all
select dateadd(hh,1,FullDateAlternateKey)
,datepart(dw,dateadd(hh,1,FullDateAltern
ateKey))
,datepart(hh,dateadd(hh,1,FullDateAltern
ateKey))
,datename(dw,day(dateadd(dw,2,dateadd(hh
,1,FullDateAlternateKey))))
from dd
where FullDateAlternateKey<='2005-12-31'
)
select * from dd
option (maxrecursion 0)
You will notice that I have added a dateadd(dw,2,...) in the recursive part.
Try pulling it out you will find (at least that is what happens in my
system) that the day returned is two days back from the actual day.
Am I doing something wrong?
"Trey Walpole" wrote:

> The second parameter for DATENAME is a date - this call is getting the
> day of the w for 1900-01-06, which is a Saturday. The implicit
> conversion is based on 0=1900-01-01, ergo 5=1900-01-06.
> As Anith has said, DATEPART is dependent on DATEFIRST, which is probably
> set to Monday in your system.
> yurps wrote:
>

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
--
FredHi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

function dateadd

hi,
can something tell me what function do i need if i want to know what happen the last three hours?
for example: dateadd (hh, -3, getdate())
it seems not working?!!that works just fine

perhaps remove the space between dateadd and (|||it was just a small mistake that i did not consider, now it's working, actually the space has nothing to do with it.
thanks