Delete a table 2

[Saturn57]

I have a query that creates a table. Is it possible to use a command I can put in the start of the create table query to delete that table should it exist before I go to create it?

 

[RiverGuy]

Yes, but why don't you just retain the table and clear it out instead of deleting it?

Here's an example to drop the table:

IF EXISTS 
	(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SomeTableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE dbo.SomeTableName
END

 

[Saturn57]

I'm trying to delete the current date from a hiredate and divide it by 365 to get the number of years worked as follows. But I always get an error that Today is not a recognized built in function. Can anyone help me. My code is below:
SELECT EMPLOYEE, FULLNAME,HIREDATE,Sum(TODAY()-HIREDATE/365) as Years
FROM AllEmployees
UPDATE [Personnel].[dbo].[MaxDaysAllowed]
SET [EMPLOYEE] = Employee
,[FULLNAME]= FULLNAME
,[YEARS]=Years

 

[gmmastros]

use GetDate() instead of Today()

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Saturn57]

GetDate() works but it returns a date instead of a number how do I get this to return a number for the years variable.

SELECT EMPLOYEE, FULLNAME,HIREDATE,(GETDATE()-HIREDATE/365) as Years
FROM AllEmployees
UPDATE [Personnel].[dbo].[MaxDaysAllowed]
SET [EMPLOYEE] = Employee
,[FULLNAME]= FULLNAME
,[YEARS]=Years

 

[RiverGuy]

Use the DATEDIFF function. DATEDIFF(day, HIREDATE, GETDATE())/365.

 

[Saturn57]

I changed my code to this but I get an error saying: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.

GetDate() works but it returns a date instead of a number how do I get this to return a number for the years variable.

SELECT EMPLOYEE, FULLNAME,HIREDATE,(GETDATE()-HIREDATE/365) as Years
FROM AllEmployees
UPDATE [Personnel].[dbo].[MaxDaysAllowed]
SET [EMPLOYEE] = Employee
,[FULLNAME]= FULLNAME
,[YEARS]=Years

 

[Saturn57]

I took the comma out after Years and I now get this error?

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

(25 row(s) affected)

 

[gmmastros]

What is the data type for HireDate? I suspect it's not a DateTime data type. If I'm right, please show us some sample data for HireDate.

The last query you posted should have caused an error because you cannot divide a DateTime.

(GETDATE()-HIREDATE/365) as Years

Based on the order of operations, HireDate would be divided by 365 before being subtracted from GetDate(), which is not what you want.

The code posted by RiverGuy should work if HIREDATE is a DateTime or a string that can be converted to datetime (in an Un-ambiguous way).

If you are not sure what the data type is, then run this:

[tt][blue]Select Data_Type
From Information_Schema.Columns
Where Table_Name = 'AllAmployees'
And Column_Name = 'HIREDATE'
[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Saturn57]

It it not a date it is a decimal (9,0). So what do I use?

 

[gmmastros]

Please show some sample data. There are various ways to store date in a decimal(9,0). Before we can advise you, we need to know what the same data looks like.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Saturn57]

Here is what it looks like:
19930719
19940914
19870225
19970122
19980514
20010709
19980826
20000619
20000821

 

[gmmastros]

Before continuing, I want to strongly encourage you to convert your column to a DateTime column. When performing calculations on dates, it's 100 times easier if you already have a date instead of a number.

Luckily, you are already storing your dates in a way that won't be too hard to work with. (I assume that your dates are YYYYMMDD.)

To work with this, you'll need to convert to varchar, then to DateTime.

Using RiverGuy's code....

DATEDIFF(day, [!]Convert(DateTime, Convert(VarChar(8), [/!]HIREDATE[!]))[/!], GETDATE())/365

Try this:

SELECT EMPLOYEE, 
       FULLNAME,
       HIREDATE,
       DateDiff(Day, Convert(dateTime,Convert(VarChar(8), HIREDATE)), GetDate())/365 as Years 
FROM   AllEmployees

Notice how there's 2 extra conversions occurring. If you store HireDate as a DateTime column, there would be less conversions and this query would perform better because you could remove the conversion functions.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom