Data malipulation in a VIEW

[handlewithcare]

I have an application that, due to Y2K and other issues, stores dates in an unusual format.&nbsp;&nbsp;To read this data as number of days since 01/01/1900, negative values must be added by 52755 and positive values must be subtracted by 12785.&nbsp;&nbsp;I am trying to create a VIEW the will enable this to look normal.&nbsp;&nbsp;I have created a formular in ACCESS which translates the values, but I'm unsure how to write this in SQL.<br><br>IF datefield&lt;0 THEN datefield+52755 ELSE old_date-12785<br><br>If anyone has any ideas/solutions they would be gratefully recieved.<br><br>Many thanks in advance

 

[cdukes]

Try the case statement:<br><br>CASE WHEN (datefield &lt; 0 )<br>THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;datefield + 52755 <br>ELSE<br>&nbsp;&nbsp;&nbsp;&nbsp;datefield - 12785<br>END<br><br>Hope this helps,<br><br>Chris Dukes

 

[handlewithcare]

Thanks,<br><br>I've tried to include this into a SELECT statement, but I'm getting an invalid statement error. Any ideas?<br><br>SELECT <br>CASE WHEN (datafield &lt; 0 )<br>THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;datafield + 52755 <br>ELSE<br>&nbsp;&nbsp;&nbsp;&nbsp;datafield - 12785<br>END<br>FROM table<br><br>Thanks

 

[cdukes]

The following works fine on a table called 'tblCallsHeader' which has a date field CallDate. It will return all the data from the 'tblCallsHeader' and the converted date in a new field 'NewData'<br><br>SELECT *,<br>CASE WHEN (CallDate &lt; 0)<br>THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;CallDate + 52755 <br>ELSE<br>&nbsp;&nbsp;&nbsp;&nbsp;CallDate - 12785<br>END NewDate<br>FROM tblcallsHeader<br><br>What RDBMS are you using and how is the date actually being held. The above works fine for SQLserver as dates are held internally as numberic values. you may have a conversion problem when adding values to the date.<br><br>Chris Dukes.

 

[handlewithcare]

Thanks for trying this, I suspect it's a problem with my RDBMS.&nbsp;&nbsp;As a test removed simplified the statement to:<br>SELECT *,<br>CASE WHEN (Datafield &lt; 0)<br>THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;52755 <br>ELSE<br>&nbsp;&nbsp;&nbsp;&nbsp;12785<br>END NewDatafield<br>FROM tbltest<br><br>I'm using SQLScope with Pervasive.SQL 7.&nbsp;&nbsp;I've also tried using Microsoft Query, but unable to get it to work with that either.<br><br>Thanks, Richard<br>

 

[cdukes]

I have never used Pervasive.SQL 7 so your best bet probably is to<br>look up the SQL command syntax documentation for the syntax to the CASE statement if it supports it.<br><br>The CASE statement is standard SQL, but your database might have a different syntax or might not even support it.<br><br>Chris Dukes