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.
| Author |
Topic |
|
rme8494
Yak Posting Veteran
98 Posts |
 Posted - 2003-07-26 : 16:08:27
|
| Hi Everyone,I'm writing a stored procedure and I need to set a date time variable. This variable needs to read the first of what ever month and year it is now. For example today that variable would be 07/01/2003. How do I go about that? I'm tyring the following code without much luck.DECLARE @closeddate DATETIMESELECT @closeddate = MONTH(getDate())/1/YEAR(getDate())ThanksRyanRyan EverhartSBC |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-26 : 17:47:43
|
| Date's can be tricky to manipulate in T-SQL. in the script library I have posted a handy function called MDate you might want to look at (do a search).without using a custom function, here's one method:Step 1: DateDiff(mm, 0, GetDate() )returns the # of months between the current month and the "base date", or a value of 0. this base date doesn't matter, it can be any date -- but it must be the first day of the month for that date.Final result: DateAdd(mm, 0, [result from Step 1])returns the result, because we are taking the result of the first half (@t) and adding that many months (using the DateAdd() function) BACK to that arbitrary "base date" of 0. The result? the first day of the month of GetDate().SO, the final answer is:DateAdd(mm, 0, DateDiff(mm, 0, GetDate()))- Jeff |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-27 : 02:53:16
|
| Excellent explanation, Jeff!Just a small correction there:>>Final result: DateAdd(mm, 0, [result from Step 1]) Thats supposed to be DateAdd(mm, [result from Step 1], 0)since we want to add the number of months obtained to the base date (rather than add 0 days to the number of months).Hence the magical formula isDateAdd(mm, DateDiff(mm, 0, GetDate()), 0) Owais |
|
|
|
rme8494
Yak Posting Veteran
98 Posts |
Posted - 2003-07-27 : 13:34:27
|
| Thanks for your help Jeff... below is the code thta works for me!!SELECT DateAdd(mm, DateDiff(mm, '1/1/03', GetDate()), '1/1/03') Thanks again..RyanRyan EverhartSBC |
|
|
|
|
|
|
|
|
SQL Variables