SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 DatePart Enumerator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AlbreK01
Starting Member

2 Posts

Posted - 01/06/2011 :  09:15:22  Show Profile  Reply with Quote
Is there a way to get to the enumerator list of available defined DatePart definitions?

I need to develop a SP to allow the caller to specify the datepart that is used in the validation of a date field. I am trying to keep this validation logic on the server side but I want the SP to generate a raiserror if the caller sends an invalid "DatePart".

Thank you in advance.

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/06/2011 :  11:20:34  Show Profile  Reply with Quote
You can go to Books On Line and look at the datepart function where all the permissible parts and abbreviations are listed. Couldn't you limit the user to choosing only valid date parts?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

AlbreK01
Starting Member

2 Posts

Posted - 01/06/2011 :  13:32:18  Show Profile  Reply with Quote
I ended up writing a function to validate the DatePart the caller passes in . . . Such like so . . .

select @DatePart =
case
when (upper(@UserDatePart) in ('YEAR', 'YEARS', 'YY', 'YYYY', 'YR')) then 'yy'
when (upper(@UserDatePart) in ('QUARTER', 'QUARTERS', 'QTR', 'QQ', 'Q')) then 'qq'
when (upper(@UserDatePart) in ('MONTH', 'MONTHS','MM', 'M')) then 'mm'
when (upper(@UserDatePart) in ('DAYOFYEAR', 'DY', 'Y')) then 'dy'
when (upper(@UserDatePart) in ('DAY', 'DAYS', 'DD', 'D')) then 'dd'
when (upper(@UserDatePart) in ('WEEK', 'WEEKS', 'WK', 'WW')) then 'ww'
when (upper(@UserDatePart) in ('WEEKDAY', 'DW', 'W', 'WD')) then 'dw'
when (upper(@UserDatePart) in ('HOUR', 'HOURS', 'HH', 'H', 'HR')) then 'hh'
when (upper(@UserDatePart) in ('MINUTE', 'MINUTES', 'MI', 'N')) then 'mi'
when (upper(@UserDatePart) in ('SECOND', 'SECONDS', 'SS', 'S', 'SEC')) then 'ss'
when (upper(@UserDatePart) in ('MILLISECOND', 'MS')) then 'ms'
when (upper(@UserDatePart) in ('MICROSECOND', 'MCS')) then 'mcs'
when (upper(@UserDatePart) in ('NANSECOND', 'NS')) then 'ns'
else null
end
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000