Getting 1 answer from 2 other fields. 1

[diskmonitor]

Hi,

I'm very new to SQL so I'm hoping that someone can help me with some step-by-step tips.

I've got a database table called 'ITinventory' which has three fields :

- Warranty Time
(this would have 1,2 or 3 which would correspond with
1year, 2years or 3 years).

- Warranty Expiry
- Purchase Date

I would like to have a query (or something more suitable if necessary) to read data from 'Purchase date', add the amount of days from 'Warranty Time' and display the answer in 'Warranty Expiry'. Bearing in mind that the 'Warranty Time' is in values of 1,2 or 3.

Many thanks for any help, its very much appreciated.

Scott

 

[bborissov]

SELECT PurchaseDate,
       WarrantyTime,
       DateAdd(year, WarrantyTime, PurchaseDate) AS WarrantyExpiry
FROM YourTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[diskmonitor]

Thank you for the code but it seems to fail on 'DateAdd' - it says 'Incorrect syntax'

Thanks

Scott

 

[Crowley16]

check your typing, column names, table names...

--------------------
Procrastinate Now!

 

[markros]

Make sure you don't have invalid entries in the WarrantyTime.

I just tested that code and it worked fine for me.

PluralSight Learning Library

 

[diskmonitor]

Thank you for your help everyone. Would this be the correct code ? :

SELECT [Purchase Date]
[Warranty Time]
DateAdd (year, [Warranty Time], [Purchase Date]) AS [Warranty Expiry]
FROM ITInventory


It keeps saying 'Incorrect syntax near 'DateAdd'

Would it matter if some of the fields were NULL (there are still around 100 entries which have purchase date and warranty time populated) ?

 

[bborissov]

Sure, you missed several commas:

SELECT [Purchase Date][COLOR=red][b],[/b][/color]
       [Warranty Time][COLOR=red][b],[/b][/color]
       DateAdd (year, [Warranty Time], [Purchase Date]) AS [Warranty Expiry]
FROM ITInventory

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[diskmonitor]

That worked like a charm Thank you so much.

How do I commit the change to the field and is there a way of getting just the date (rather than having 01/01/2015 00:00:00).

Many thanks

Scott

 

[bborissov]

That is a job for your front end. There you should interpret the datetime as date.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.