Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AlbreK01
Starting Member
2 Posts |
 Posted - 2011-01-06 : 09:15:22
|
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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-01-06 : 11:20:34
|
| 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?JimEveryday I learn something that somebody else already knew |
 |
|
|
AlbreK01
Starting Member
2 Posts |
Posted - 2011-01-06 : 13:32:18
|
| 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 |
|
|
|
|
|
|
|
|
DatePart Enumerator