sql - Get date for nth day of week in nth week of month -

-

i have column values '3rd-wednesday', '2nd-tuesday', 'every-thursday'.

i'd create column reads strings, , determines if date has come month, , if has, return date of next month. if has not passed yet month, return date month.

expected results (on 4/22/16) above be: '05-18-2016', '05-10-2016', '04-28-2016'.

i'd prefer mathematically , avoid creating calendar table if possible.

thanks.

partial answer, no means bug free.

this doesn't cater 'every-' entries, give inspiration. i'm sure there plenty of test cases fail on, , might better off writing stored proc.

i did try calculating day name , day number of first day of month, calculating next wanted day , applying offset, got messy. know said no date table cte simplifies things.

how works

a cte creates calendar current month of date , dayname. rather suspect parsing code pulls day name test data , joins cte. clause filters dates greater nth occurrence, , select adds 4 weeks if date has passed. or @ least that's theory :)

i'm using datefromparts simplify code, sql 2012 function - there alternatives on 2008.

select * #test (values ('3rd-wednesday'), ('2nd-tuesday'), ('4th-monday')) a(value)  set datefirst 1  ;with days (     select      cast(dateadd(month,datediff(month,0,getdate()),n.number) date) date,      datename(weekday, dateadd(month,datediff(month,0,getdate()),n.number)) dayname      master..spt_values n n.type = 'p' , n.number between 0 , 31 ) select      t.value,     case when min(d.date) < getdate() dateadd(week, 4, min(d.date)) else min(d.date) end date #test t join days d on reverse(substring(reverse(t.value), 1, charindex('-', reverse(t.value)) -1)) = d.dayname d.date >=     datefromparts(         year(getdate()),          month(getdate()),         1+ 7*(cast(substring(t.value, 1,1) int) -1)         ) group t.value    value         date ------------- ---------- 2nd-tuesday   2016-05-10 3rd-wednesday 2016-05-18 4th-monday    2016-04-25

Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more