Welcome

**Matt_tullie** · 24-02-2010 10:10:28

Matt_tullie
Veteran
Offline

From: Carlisle, Cumbria
Registered: 18-05-2007
Posts: 166

Re: Joy and dbo.LCReturnVagueDateShort

Just unravelling the reporting of vague dates - not for the first time I should add, but this time I'm determined to CRUSH the issue out of existence.

Any road, using dbo.LCReturnVagueDateShort (see - http://forums.nbn.org.uk/viewtopic.php?id=305) seems to be lovely, except when vague_date_type = 'M'

In this instance, the value returns NULL. Which is odd, considering all other VDTypes return what would seem to be appropriate values, even the seasons!

Ive checked the Sample Keys and they seem to exist as months only in recorder which is fine.

Can anyone confirm this behaviour and/or solve the mystery?

select dbo.LCReturnVagueDateShort(sample.vague_date_start,sample.vague_date_end,sample.vague_date_type) as Recorder_date
from sample
where sample.vague_date_type = 'M'

I'm SO close to joy!

M

Cumbria Biodiversity Data Centre
Tullie House Museum

**RobLarge** · 24-02-2010 10:35:58

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Matt, there is a clause missing within the code of dbo.LCReturnVagueDateShort so it doesn't handle the month only type 'M'. I don't know why this is, or whether it matters if you put the clause back in, but to be on the safe side, if you edit this function you should probably save it under a different name.

Anyway if you can open the code up in Managment Studio, or whatever you have, and look near the bottom for this bit.

set @RP1 = case @VD
when 'D' then @RDS1 + '/' + @RMS1 + '/' + str(@RY1,4,0)
when 'DD' then @RDS1 + '/' + @RMS1 + '/' + str(@RY1,4,0) + '- ' + @RDS2 + '/' + @RMS2 + '/' + str(@RY2,4,0)
when 'O' then @MM1 + ' ' + str(@RY1,4,0)
   when 'OO' then @MM1 + ' ' + str(@RY1,4,0) + ' - ' + @MM2 + ' ' + str(@RY2,4,0)
   when 'Y' then str(@RY1,4,0)
   when 'YY' then str(@RY1,4,0) + ' - ' + str(@RY2,4,0)
   when '-Y' then ' - ' + str(@RY2,4,0)
   when 'Y-' then str(@RY1,4,0) + ' - '
   when 'C' then str(@CC1,2,0) + 'c'
when 'CC' then str(@CC1,2,0) + 'c - ' + str(@CC2,2,0) +'c'
when '-C' then ' - ' + str(@CC2,2,0) + 'c'
when 'C-' then str(@CC2,2,0) + 'c - '
when 'U' then 'Unknown'


when 'M' then @MM1

end

The line in bold at the bottom is the one you need to add to get a result out for month only.

Hope this brings you much joy!

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**Matt_tullie** · 24-02-2010 11:10:03

Matt_tullie
Veteran
Offline

From: Carlisle, Cumbria
Registered: 18-05-2007
Posts: 166

Re: Joy and dbo.LCReturnVagueDateShort

Get that JOY chilled and READY to pop!

Rob thats great, thank you.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

**RobLarge** · 24-02-2010 11:38:16

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Matt, I'd love to know, what, in your view , is the value of the M vague date type (or S for that matter)?

We have a few samples in our dataset with this type of date, but to me they just feel like clutter.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**MikeWeideli** · 04-03-2010 14:55:12

MikeWeideli
Administrator
Offline

From: Littlefield Consultancy
Registered: 05-07-2006
Posts: 2,627

Re: Joy and dbo.LCReturnVagueDateShort

Thanks for spotting this. My mistake in the original code. I suspect it will bo OK to fix the exiting routine as an updated version should be in the next relesae of Recorder 6. M isn't used in many databases, which is why I suspect it hasn't come to light before. I think all the possibilities are covered now.

Mike Weideli

**RobLarge** · 04-03-2010 15:26:38

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Apart from S of course

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**MikeWeideli** · 05-03-2010 08:28:58

MikeWeideli
Administrator
Offline

From: Littlefield Consultancy
Registered: 05-07-2006
Posts: 2,627

Re: Joy and dbo.LCReturnVagueDateShort

I tested S and its seems to work ?

Mike Weideli

**RobLarge** · 05-03-2010 09:40:53

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Fair enough Mike, I didn't test it, I was just looking at that code excerpt above and it doesn't seem to have a "When 'S'" clause.

Those extremely vague date types M and S seem rather pointless to me anyway, I wouldn't be sorry if they were unused in our dataset, unfortunately that isn't the case.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**Matt_tullie** · 05-03-2010 14:21:10

Matt_tullie
Veteran
Offline

From: Carlisle, Cumbria
Registered: 18-05-2007
Posts: 166

Re: Joy and dbo.LCReturnVagueDateShort

Rob,

M and S are indeed fairly useless, BUT we have them in our database and hence they have value.
Until such time as I can go back and unravel them, i cant be certain whether they can be pinned down to a better class of date or not.

As a lot of our data has been input over many years in recorder3, and may relate to Museum specimens, personal collections and historical accounts, Season or Month can be the best and most truthful entry you can provide (until an unknown future time when more information comes to light!).

So, while I agree that new data should avoid these classes if at all possible, historical data holdings may not behave in that way!

MAtt

Cumbria Biodiversity Data Centre
Tullie House Museum

**RobLarge** · 05-03-2010 15:11:51

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Agreed, if grudgingly!

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**johnvanbreda** · 05-03-2010 15:37:39

johnvanbreda
Administrator
Offline

From: UK
Registered: 27-04-2006
Posts: 1,237

Re: Joy and dbo.LCReturnVagueDateShort

Consider the poor museum curator with an old label that just says May followed by a splodge!

John van Breda
Biodiverse IT

**RobLarge** · 05-03-2010 16:34:39

RobLarge
Veteran
Offline

From: Wiltshire
Registered: 15-07-2008
Posts: 812

Re: Joy and dbo.LCReturnVagueDateShort

Oh I do John, there but for the grace and all...

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

**Matt_tullie** · 08-03-2010 13:38:16

Matt_tullie
Veteran
Offline

From: Carlisle, Cumbria
Registered: 18-05-2007
Posts: 166

Re: Joy and dbo.LCReturnVagueDateShort

We give 'splodges' a special category.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Welcome

Joy and dbo.LCReturnVagueDateShort

Posts: 13

1 Reply by Matt_tullie 24-02-2010 10:10:28

Re: Joy and dbo.LCReturnVagueDateShort

2 Reply by RobLarge 24-02-2010 10:35:58 (edited by RobLarge 24-02-2010 11:33:38)

Re: Joy and dbo.LCReturnVagueDateShort

3 Reply by Matt_tullie 24-02-2010 11:10:03

Re: Joy and dbo.LCReturnVagueDateShort

4 Reply by RobLarge 24-02-2010 11:38:16

Re: Joy and dbo.LCReturnVagueDateShort

5 Reply by MikeWeideli 04-03-2010 14:55:12

Re: Joy and dbo.LCReturnVagueDateShort

6 Reply by RobLarge 04-03-2010 15:26:38

Re: Joy and dbo.LCReturnVagueDateShort

7 Reply by MikeWeideli 05-03-2010 08:28:58

Re: Joy and dbo.LCReturnVagueDateShort

8 Reply by RobLarge 05-03-2010 09:40:53

Re: Joy and dbo.LCReturnVagueDateShort

9 Reply by Matt_tullie 05-03-2010 14:21:10

Re: Joy and dbo.LCReturnVagueDateShort

10 Reply by RobLarge 05-03-2010 15:11:51

Re: Joy and dbo.LCReturnVagueDateShort

11 Reply by johnvanbreda 05-03-2010 15:37:39 (edited by johnvanbreda 05-03-2010 15:37:57)

Re: Joy and dbo.LCReturnVagueDateShort

12 Reply by RobLarge 05-03-2010 16:34:39

Re: Joy and dbo.LCReturnVagueDateShort

13 Reply by Matt_tullie 08-03-2010 13:38:16

Re: Joy and dbo.LCReturnVagueDateShort

Posts: 13