TroubleShooting Data Conversion Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TroubleShooting Data Conversion Error

Author

Topic

ScottFree
Starting Member

3 Posts

Posted - 2010-02-05 : 04:35:04

Hi folks,

I'm a complete newbie to SQL server and I'm royally stuck on this command.

The idea is simple:
IF DateJoin is less than 1st Day of the Year, then use DateResign - 1st Day of the Year / 365. Else use the DateJoin.

The execution though has eluded me, here's my command:

CASE
when (([EM].[DateJoin] - Convert(datetime,'1/1/'&Year(getdate()))) > 0) then Round(([EM].[DateResign] - [EM].[DateJoin])/365,3)
When (([EM].[DateJoin] - Convert(datetime,'1/1/'&Year(getdate()))) <= 0) then Round(([EM].[DateResign] - Convert(datetime,'1/1/'&Year(getdate())))/365,3)
Else 0
End

I keep getting this error "implicit data conversion from datetime to int" when I adjust to Convert(datetime,'1/1/2010'). I get a syntax error.

Any help or advice at all would be immensely appreciated. Thank you all in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 04:41:15

try


CASE
WHEN	[EM].[DateJoin]	>= dateadd(year, datediff(year, 0, getdate()), 0)
THEN	ROUND(DATEDIFF(DAY, [EM].[DateJoin], [EM].[DateResign]) / 365.0, 3)
ELSE	ROUND(DATEDIFF(DAY, dateadd(year, datediff(year, 0, getdate()), 0), [EM].[DateResign]) / 365.0, 3)
END

Note : dateadd(year, datediff(year, 0, getdate()) will give you the 1st day of current year

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 04:44:11

Use 'yyyymmdd' for your implicit date conversions, or use a specific CONVERT parameter to indicate the format you are using.

SELECT CONVERT(datetime, '20100101')
SELECT CONVERT(datetime, '1/1/2010', 101)

Where 101 implies m/d/y - use 103 if it is d/m/y, see SQL Documentation of CONVERT for other format values

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 04:44:15

The condition should be:
YEAR(DateJoin) < YEAR(GETDATE())

But I cannot understand what to do if the condition is true or not...

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 04:45:19

Minor correction:

Note : dateadd(year, datediff(year, 0, getdate()), 0) will give you the 1st day of current year

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 04:48:27

quote:
Originally posted by Kristen

Minor correction:

Note : dateadd(year, datediff(year, 0, getdate()), 0) will give you the 1st day of current year

Thanks. I think i should get out of here now

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 05:29:56

You and me both!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-05 : 05:35:21

Sample data with wanted output would make it more clear...

No, you're never too old to Yak'n'Roll if you're too young to die.

ScottFree
Starting Member

3 Posts

Posted - 2010-02-06 : 00:26:23

Hey good samaritans at SQLTeam!
Especially khtan and kristen! Thank you thank you.
The formula worked! :) I was crackin my noggin on this for 2 days.

So i guess I had the order wrong plus this bit: Convert(datetime,'1/1/'&Year(getdate()))) doesn't work.
Would it have worked if i had added the styleID 103 as advised?

Thanks again everybody!

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 01:37:02

"Would it have worked if i had added the styleID 103 as advised?"

103 is d/m/y so that's part of the issue.

but Convert(datetime,'1/1/'&Year(getdate())))

has several other issues I'm afraid!

"&" should be "+" for string concatenation

"Year(getdate())" returns a numeric result, so needs to be converted to String before it can be concatenated

so you would wind up with

Convert(datetime, '1/1/' + CONVERT(varchar(4), Year(getdate())), 103)

However, if I was doing it this way I would use

Convert(datetime, CONVERT(varchar(4), Year(getdate())) + '0101')

to use the preferred 'yyyymmdd' implicit cast format. Both come to the same thing though.

This requires type conversions in converting GetDate() to Year, again for Year to Varchar, and again for the String to Datetime, and is thus sadly inefficient.

Hence the preferred, if rather unreadable!:

DATEADD(year, DATEDIFF(year, 0, getdate()), 0)

to get the 1st of Jan for the current year. All these functions operate using DATETIME datatype, and the absence of datatype changes makes this the most efficient method.

ScottFree
Starting Member

3 Posts

Posted - 2010-02-09 : 08:37:06

Dear Kristen thank you so much taking time out to do this mini tutorial!
Once again really appreciate the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources