1 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Displaying SQL Date

I remember reading somewhere on this forum about how to sort out displaying of normal date format as opposed to the SQL coded number for the date but can not find the message. Can anyone direct me please to it?

The problem I have is using the snapshot and displaying the data in ArcGIS and the Start and End dates are just the coded numbers and not the dates. Not very helpful. I want to be able to open the attribute table and see the date.

Thanks

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Displaying SQL Date

There's this: http://eim.metapath.org/wiki/index.php?title=Vague_Dates

Mike Weideli has also produced a UDF that does the job more reliably. He said I could post it onto the Wiki, so I'll see if I can get around to it today.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Displaying SQL Date

Hi Charles,

The wiki was very informative thankyou, but I was unable to get the code to work for me. So I look forward to Mike's alternative.

I wonder whether it is possible that when recorder produces the snapshot it then converts the INT to DATETIME based on whether all records are after 01/01/1753. This would solve the snapshot problem at least.

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

4 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Displaying SQL Date

Brian

The bit of information you want on the Wiki is the following SQL example:

SELECT    CONVERT(char(10), DATEADD(DAY, SAMPLE.VAGUE_DATE_START, '1899-12-30'), 103) AS Dates
FROM    SAMPLE
WHERE    SA.VAGUE_DATE_START >= -53688

This shows the SQL you can use to get a field from its integer value to a datetime (CONVERT(char(10), DATEADD(DAY, SAMPLE.VAGUE_DATE_START, '1899-12-30'), 103) AS Dates).

Will that not work for you?

John van Breda
Biodiverse IT

johnvanbreda's Website

5 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Displaying SQL Date

johnvanbreda wrote:

Brian

The bit of information you want on the Wiki is the following SQL example:

SELECT    CONVERT(char(10), DATEADD(DAY, SAMPLE.VAGUE_DATE_START, '1899-12-30'), 103) AS Dates
FROM    SAMPLE
WHERE    SA.VAGUE_DATE_START >= -53688

This shows the SQL you can use to get a field from its integer value to a datetime (CONVERT(char(10), DATEADD(DAY, SAMPLE.VAGUE_DATE_START, '1899-12-30'), 103) AS Dates).

Will that not work for you?

Hi John,

I did see that bit of code at the bottom of the wiki. Yes, it probably would work for me, but I do not know how to get it to work for me. It appears to me that the example is for a specific date, although actually it is not is it? In fact it adds some confusion to my understanding instead. Why is 1899-12-30 refered to a base date, I thought 1753 was the base date (prior to this date SQL will not recognise it)?

Going back to the issue in hand though, am I changing the NBNData database with this (I assume using Query Analyzer), or am I changing my snapshot created database? If the latter then this is where I have got stuck in particular, my table is not called SAMPLE and the field is actually called SAMPLE_VAGUE_DATE_START (and END of course). And then after the WHERE clause, I have created a new Snapshot database with a separate user called recsnap, so should I be replacing SA. with recsnap.<my table>SAMPLE_VAGUE_DATE_START ?


Thanks

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

6 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Displaying SQL Date

Hi Brian

This was intended as just a rough example.  Although SQL Server uses 1753 as the start of its date range, we use 1899-12-30 as the zero date for our own dates.  The reason for this choice was simply that using CINT on a date in Access also uses 1899 as the zero date, so conversions in Access are simplified.

Assuming you have a snapshot table called x and a integer "date" field called y, here's the SQL:

SELECT top 10 DATEADD(DAY, y, '1899-12-30') AS MyDate FROM x
John van Breda
Biodiverse IT

johnvanbreda's Website

7 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Displaying SQL Date

Hi John,

Thanks for that. I managed to get it to display the dates for all the records, not just the first 10, which is great. However, going back to my first initial problem of opening the attribute table in the GIS this will not actually display the dates.

Having discussed this on the ESRI user forum someone replied saying "the only way I can see of getting around this is to create a View in SQL Server that Casts the Int field as a DateTime field and then change you ArcGIS connection to point to this view, rather than the table".

I am now in the process of seeing what can be done using this method, but I do feel this is still not ideal. Surely the snapshot tool should be doing this conversion process for us the user. I can not see other staff here using the snapshot tool (which I do like and think is great in the main) if they can not see the date of the record, especially as they are not technically minded. They merely want to click on a button and it does it. This would be nice.

I wonder what the reality is, whether many people have records earlier than 1753 or earlier than 1899. If not why not make the date fields DATETIME.

Many thanks

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website