Conditional Date selection - Help needed

Question

Post reply

Conditional Date selection - Help needed

Helen McCallum

Mr or Mrs. 500

Points: 583
More actions
November 26, 2004 at 3:41 am

#59811

Hi, I have a table of employees that contains a start_date from which I am creating a view for ODBC reporting.
I am creating a date called anniversary in the view using Dateadd(month,6,start_date) and that works fine.
My problem is that I would like to set the anniversary date to a set date where the start date is before 1st May 2004.
Guess there must be a way but I'm struggling. Anyone out there got any ideas? Thanks in advance.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

Hi Helen,

Does a straight WHERE clause not fulfill your requirements.

SELECT PK, StartDate FROM DateTest

WHERE StartDate > CAST('01/05/2004' as datetime)

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Helen,

can you explain this with an example?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 3

CASE WHEN start_date < '2004-05-01'

THEN '2004-05-01'

ELSE Dateadd(month,6,start_date)

END AS [anniversary]

Far away is close at hand in the images of elsewhere.
Anon.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Hi Gents,

And its not even a Dynamic SQL question.......

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Okay, some Friday funnies:

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 6

Nice one Frank,

I will use that one on my kids.......

Have a good weekend.

Best Regards

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

You, too.

Here's one more: http://www.baetzler.de/humor/null.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Helen McCallum Mr or Mrs. 500 Points: 583 More actions · Answer 8

David, Thanks for your help it works a dream.

Regarding the funnies, I will take them in the spirit they were meant (I hope)

Were you never beginners????

David.Poole SSC Guru Points: 76035 More actions · Answer 9

One thing to watch out for is the default language under which your server is configured.

If it is US_English then CAST('01/05/2004' AS SMALLDATETIME) will give 5th January 2004.

To get around this use SET DATEFORMAT MDY or SET DATEFORMAT DMY before your cast statement.

The old fashioned way was to submit your date to a stored procedure as a string of known format and then use CONVERT(SMALLDATETIME,@param,103).

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Regarding the funnies, I will take them in the spirit they were meant (I hope)

Gee, no. The funnies had nothing to do with your question!!!

Please, don't get this wrong. If you felt offended in any way, I apologize for this.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65143 More actions · Answer 11

Regarding the funnies, I will take them in the spirit they were meant (I hope)

Helen,

The humerous anecdotes are always intended to be innocent, innocuous and never personal (with a few exceptions) whether they refer to the subject in question or not. I have never known Frank to insult anyone ( nice chap ).

Were you never beginners????

Yep and still am. My knowledge is only surpassed by my stupidity

Far away is close at hand in the images of elsewhere.
Anon.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 12

Helen,

There was definatley no intention to insult or offend. It was just Friday and wind down.....

Frank, David - I think it must be me

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Wrong!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Hey, I should have rather posted this instead:

http://dsc.discovery.com/news/afp/20040823/psychopath.html

And identify the common "management enemy"

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]