Creating Date in Select Statement

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

SQL Server 2000 Forums

SQL Server Development (2000)

Creating Date in Select Statement

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-02-24 : 08:42:57

[b]
Hi:
I want to check whether the employee termination date falls between 21st of the previous month till 20th of the current month range. This should not be static. I fear it might be a silly solution but I am unable to find it.

Any help will really be appreciated.

Bye,
Marjo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-24 : 09:16:15

T-SQL has date manipulation functions, but the way to create dates is a little tedious.

Look at the "SCRIPTS" section of this forum; I have posted a function called MDate() that is the equivalent of the DateSerial() function in visual basic, which lets you create dates pretty easily.

otherwise, you need to concatenate a string together and then convert it to a date datatype.

i.e.,

CONVERT(DateTime, Convert(Varchar(4),Year) + '-' + Convert(Varchar(2),Month) + '-' + Convert(Varchar(2),Day))

With the function, you can just

MDate(Year,Month,Day)

and also do date-math pretty easily.

- Jeff

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-02-24 : 22:49:25

Hi JSmith:
Thanks for replying, I tried my best to search for ur post with "SCRIPTS" title but unfortunately I couldn't find it. Can U please send me the contents or link to that post ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-24 : 23:18:59

Actually, you can tell if it falls before the 20th of the current month with:

SELECT CASE WHEN DateDiff(month, DateAdd(day, -20, TermDate), getdate())=0 THEN 'This Month' ELSE 'Last Month' END
FROM MyTable

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-25 : 07:40:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

There's definitely other ways -- i.e., Rob's post showing how to do half of the criteria you mentioned -- but I think it is easier to code, debug and understand your code if you use a function for this sort of thing.

Anyway, with MDate() you can just say

CASE WHEN YourDate BETWEEN dbo.MDate(Year(GetDate()),Month(GetDate()),20) AND dbo.MDate(Year(GetDate()),Month(GetDate())-1,21) THEN 1 ELSE 0 END

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources