SQL Server – Subtracting with GetDate()

sql serversql-server-2016t-sql

I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql
round(cast((Current_Date - emp.hiredate) As Decimal)/365,1)

now I thought it would be a simple conversion and I tried to change it for MS SQL to

round(cast((GetDate() - emp.hiredate) as decimal) / 365, 1)

but in MS SQL Server I get this error

The data types date time and date are incompatible in the subtract operator

Best Answer

So, you are looking for the difference in Years between the two dates, correct? And you want to show one decimal place in the calculation, correct? Then a simple DATEDIFF() will not work for you. Try this instead:

SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1)

This is also imprecise (because of leap years) but matches what you have now.

EDIT:
You might want to check this logic, as the current ROUND() logic would show a person at 1.0 years when they hit 0.95 years. If you want to wait until they actually have 1.0 years or more to show them at 1.0 years, use this variant: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1, 1). This adds a trailing 1 to the current command. That added parameter TRUNCATES at the point specified instead of rounding up, so 0.99' would still show '0.9'

Related Solutions

SQL Server – How to Convert VARCHAR to DATETIME Format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Sql-server – Use Distinct With Cast As XML

You have 2 issues that I can see:

You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "' 
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql 

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.

Best Answer

Related Solutions

SQL Server – How to Convert VARCHAR to DATETIME Format

Sql-server – Use Distinct With Cast As XML

Related Question