Problem with simple case - SQL Server Forums

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

Problem with simple case

Author

Topic

vkkishore_s
Starting Member

5 Posts

Posted - 2009-11-04 : 09:31:08

dear all

can any body tell me what is the err in the below query
select
CASE WHEN YEAR(getdate()) = '2009' THEN '20090101' ELSE getdate() END AS JOININGDATE

i am getting out put as : 2009-01-01 00:00:00.000

if i replace getdate() with any number or empty string in else part then i am getting as '20090101'

why this is happening

please advice me

regards
kishore

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-04 : 09:55:54

You are mixing and matching data types, getdate is datetime and '20090101' is character. YEAR(getdate()) is an integer,'2009' is a character. Please give an example of the output you'd like to see. YEAR(getdate()) will always return 2009 until next year.

Jim

Everyday I learn something that somebody else already knew

vkkishore_s
Starting Member

5 Posts

Posted - 2009-11-04 : 10:30:48

Dear Jim

Thanks for ur responce.

I want the output as 20090101 for the first SQL query with getdate()
in else part.

Thanks in advance
kishore

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-04 : 11:16:06

Can't do it. They're either both datetime or both character. This is a formatting issue anyway, and should be done in the front end. Very strange you want 20090101 for this year and 2010-11-04 10:15:22.693 for next

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-04 : 11:47:08

I'm also confused as to what you are trying to do, but maybe this example will help??

-- Setup Sample Data
DECLARE @Yak TABLE (JoinDate DATETIME)

INSERT @Yak
SELECT GETDATE()
UNION ALL SELECT GETDATE() + 90
UNION ALL SELECT GETDATE() - 40

-- Run Query
SELECT
	CASE 
		WHEN YEAR(JoinDate) = 2009 THEN '20090101' 
		ELSE CONVERT(VARCHAR(8), JoinDate, 112) 
	END AS JOININGDATEString,
	CASE 
		WHEN YEAR(JoinDate) = 2009 THEN CAST('20090101' AS DATETIME)
		ELSE DATEADD(DAY, DATEDIFF(DAY, 0, JoinDate), 0)
	END AS JOININGDATE
FROM
	@Yak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources