Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts

Monday, March 19, 2012

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:
>

Friday, March 9, 2012

Function like ToString

Hi

I want to deleveop a dateTime datatype for Arabic Calender.

how can i write a fucntion like ToString() function?

select ArabicDateTime::GetDate().ToWeek()

thanks every body

I don't quite understand what you are looking for. You can write your own User Defined Types (UDT's), and you can do it using the CLR. Look in BOL about UDT's for that. In that scenario, you would then just write a method on the UDT which would be whatever you wanted it to be.

Niels
|||Thank you nielsb

I write function ToString() like this

VB Code

Public Overrides Function ToString() As String
Return Me.Year & "/" & Me.Month & "/" & Me.Day
End Function


and i can call this Function in SQL Server :

SQL Code

select PersianDateTime::GetDate().ToString()



now i want to write a function named Toweeday() and call it in SQL server like this:

Code Snippet

select PersianDateTime::GetDate().Toweeday()



but i've problem. my Function Code is:


Code Snippet

public Overrides Function Toweeday() As String

Return Me.Yearname & " " & Me.Monthname & " " & Me.Dayname
End Function



VB.Net notify me to Delete Overrides Keyword. and i must use Shared keyword. and when i use Shared keyword then i cant use Me.Yearname.

thank you agin

|||

Mohsen,

Just remove the Overrides keyword and don't use Shared either. You only need Overrides when there is a method on the base class that you wish to replace the functionality of in your base class. Since Toweeday is not defined on the the base class of your class, you don't need to override it. Object is the base class for all classes, and it is where ToString is defined, which is why you need Overrides for ToString. Shared makes the method an aspect of the class not any particular instance of the class, so it cannot access instance fields. So, don't use that if you want to define a method which accesses instance fields.

If you would like to see an example of DateTime for other calendar systems, you might want to look at the CADateTime sample at http://www.codeplex.com/MSFTEngProdSamples/Wiki/View.aspx?title=SS2005%21Calendar-Aware%20Date%2fTime%20UDTs&referringTitle=Home.

|||thank you so much. my promlem solved.

i test it befor i make this topic but i catch this erorr

Msg 6573, Level 16, State 1, Line 0
Method, property or field 'ToWeekday' of class 'ArbicDateTime.ArabicDateTime' in assembly 'ArbicDateTime' is not static.

but now there isn't any problem. why?