DATEDIFF Case Statement help - 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

DATEDIFF Case Statement help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-14 : 08:23:06

HI Guys
I need some help
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]

This is my query so far
SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]
Which produces the following
CreatedDate 2012-12-17
StageName Live Transactions
CloseDate 2012-12-31
Age 424

When in fact the age should be 14days

Really looking forward to your help

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-14 : 08:32:58

SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,DATEDIFF(dd,[CreatedDate],[CloseDate]) as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Veera

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-14 : 08:46:42

Hi VeeranjaneyuluAnnapureddy

Thank you for providing your logic,
However i need to do it as a case statment,
Because depending on the stagename is depenant on whether the getdate or closedate is used

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-14 : 08:46:49

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-02-14 : 08:54:46

SELECT [CreatedDate]
,[StageName]
,[CloseDate]
,CASE WHEN stagename = ‘Live Transactions’ THEN DATEDIFF(dd,[CreatedDate],[CloseDate]) ELSE [CreatedDate] END as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Javeed Ahmed

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-14 : 09:15:35

HI ahmeds08

Createddate is presented in following format "2011-06-21T15:38:51.000Z"
Datetype :varchar(50)

When try running the following query

SELECT
[CreatedDate],
[StageName],
[CloseDate],
CASE WHEN
[StageName] = 'Live Transactions' THEN DATEDIFF(dd,[CreatedDate],[CloseDate]) ELSE [CreatedDate] end as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

i get the following message

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2011-06-21T15:38:51.000Z' to data type int.

Any ideas ?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-02-14 : 09:39:14

I believe i need to do the following CONVERT(varchar(10),[CreatedDate],102)

but where do i put that into the case statement ?

mandm
Posting Yak Master

120 Posts

Posted - 2014-02-14 : 11:24:26

It's because the datediff returns an integer and the CreatedDate by itself is a date type. Don't you need this?

SELECT
[CreatedDate],
[StageName],
[CloseDate],
CASE WHEN
[StageName] = 'Live Transactions' THEN DATEDIFF(dd,[CreatedDate],[CloseDate]) ELSE DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) end as Age
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources