Y'herd thisun? 

“What are linux Run Levels? How do you configure startups of linux services? Here ya go...”

from this page by smith

The magic datepart technique

TaggedCoding, Database

"Hi Smith! Remember the project that needed to allow entry of date values but the users couldn't be forced to any specific precision? I have the same requirement and can't remember how you did the precision metadata, we're on SQL Server, not Oracle, was it an PLSQL ability?"

:) Uh... you're thinkin' too hard, budd. All I did was add a sibling column to the DATE field and packed it with the id/enumval of the desired datepart precision. {0-Unknown, 1-YYMMDDTT, 2-YYMMDD, etc. }

>>added this by request 11/2013

Querying it with SQL Server is easy.  This example has the CASE part that can be pretty boilerplate.

Assuming the enum is:

public enum DateTimeResolutions
    NoDateValue = 0,
    YearMonthDayTime = 1,
    YearMonthDay = 2,
    YearMonth = 3,
    Year = 4,
    MonthDay =5

Then every time you have to query that column, you plug it into the CASE (and return an nvarchar in the format of your choice since values are no longer actual dates):

select EVTStartDate, EVTStartResolutionCode, 
case EVTStartResolutionCode
when 0 then  'Unknown'
when 2 then  DATENAME(YYYY, EVTStartDate) + 
'-' + right ('0'+convert(nvarchar(2), DATEPART(MM, EVTStartDate)),2 ) + 
'-' + right ('0'+convert(nvarchar(2), DATEPART(DD, EVTStartDate)),2 )
when 3 then  DATENAME(YYYY, EVTStartDate) + 
'-' + right ('0'+convert(nvarchar(2), DATEPART(MM, EVTStartDate)),2 )
when 4 then  DATENAME(YYYY, EVTStartDate) 
when 5 then  DATENAME(MM, EVTStartDate) + 
'-' + DATENAME(DD, EVTStartDate)
else  EVTStartDate
end as LogicalDateValue

from events
where datepart("mm", EVTStartDate) >5


I gotta laugh about my saying that you're thinking too hard, it's ironic...

The use of the code column was (as you're probably kicking yourself over now) straight forward... but for that project I spent more time of trying to get some "magic" UI.  The UI part of the release taught me that Users aren't always morons and they knew that they needed the feature and that the app couldn't read their minds... It was a bigger deal to me in designing and I stupidly built the issue up in my mind and wasted a lot of cycles trying to make the flow simpler.  In the end just building a usercontrol that combined a vanilla dropdown bound to the datepart enum sitting next to a standard date picker and exposing a combined type was what went out the door and the users just took it at face value and got to work.

So there ya go... default the enum code column to 0-Unknown and tada.


Now all you have to futz over is the data types. 

My code was for Oracle and even though my users never ever used the TT precision enum option I had to use the 7 byte DATE but if you're SS08 and you don't need times then you can cut down to 3 with the "new" SS/MySQL/Postgresql DATE type and use a tinyint for the code column.  With that you're about even sizewize with a DATETIME and that may lower the steam level when your dbas yell about the added index.

... but you lose that gain over the wire since you'll just have to pack the lighter DATE back into into a 8 byte .Net DateTime ...

... which is really just a 64bit long same as most all of the .Net numerics under the hood ...

... and that means that you have to add conversion code that isn't really converting anything after your null check ...

... But... However... and on a 64bit server... on tuesdays with a bubble sort...   


Ah the never ending silliness of trying to be a good programmer. Every tweak you use to cut a few bytes adds boxing down the chain... and then we'll just toss it in a SOAP packet anyway ;-).  

Fuggit, old friend, just toss the whole thing in a big-azz TEXT column with novelized XML tags and pull it into a dynamic anonymous var.  Seems that the old joke has truly come to accepted reality: a database according to MS's demos that are brought home to go right into operation,  only needs one table with one column and devs who can right-click-wizard their way to a bunch of boilerplate VS output.   Futzing over bits is like milking dodo birds.  Blame performance on your pipe to Amazon's cloud and the PLA catches the hell for all the ills in the code.

Note the smilies, please ;-)

home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better