January 30, 2011 at 8:24 pm
StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
EndDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +1))
ReportDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date)-2, +1))
This query gives the data from 10/01/2010 to 10/30/2010. It suppose to give me from 10/01/2009 to 10/31/2010. I am not getting that why. Can any one explain me in detail how these query pull the data as of now I have to pull data of all the months of 2009 and I am new to SQL Server.
January 31, 2011 at 5:56 am
npatel.bi (1/30/2011)
StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))EndDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +1))
ReportDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date)-2, +1))
This query gives the data from 10/01/2010 to 10/30/2010. It suppose to give me from 10/01/2009 to 10/31/2010. I am not getting that why. Can any one explain me in detail how these query pull the data as of now I have to pull data of all the months of 2009 and I am new to SQL Server.
There are two issues with your request. 1) None of the above is an actual query. It's just code. 2) It's .Net or VBScript code.
While DateDiff and DateAdd do exist in SQLServer, neither Date nor CStr() do, which tells me you're getting this out of an SSIS script task or some other location. Could you clarify where you got these strings and what they are a part of?
CStr() changes a value to a character string, that's what CStr stands for. DateAdd is a function that adds or subtracts an integer from a date of some type. DateDiff itself subtracts two dates from each other and comes up with a resulting integer.
What Date stands for, I don't know. But "m" probably stands for month. So the way to read this is from the inside out.
Take: CStr(DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
Look at the DateDiff("m",0,Date). I'm going to assume (maybe incorrectly) that Date is the current day of the report. So, DateDiff is trying to figure out the month count difference between the current day and 0. "-4" means four months ago, perhaps. ??? I'm not sure about this translation. I do understand the other bits, though.
DateAdd means "add two months to the result of the DateDiff". CStr() means "convert the result of DateAdd(DateDiff) from a date value to a string value."
Without understanding the code around these three strings, and where you got them from, though, that's the best interpretation I can give you.
January 31, 2011 at 1:10 pm
Yes, It is just a code from Script task in SSIS. I got these code from one of my SSIS package in that he set a date variable then execute
stored procedure which basically load the data in temp table with some predefine parameters and then load it to server on perticular location.
My Question is when I just execute "DATEDIFF("m", 0, Date)" it gives me 1333 months. Means its getting months from 01/01/1900 to till today.
and when I execute the DATEADD("m", DATEDIFF("m", 0, Date) -4, +2)) it gives me 10/01/2010.
What is the meaning of 0 and 'Date' in this code.
January 31, 2011 at 1:22 pm
Date is today.
0 is 1/1/1900, it's just a baseline number. It would appear they're trying to do time/month component strips.
For example, if I want the first day of this month I'd use DATEADD( m, DATEDIFF( m, 0, Date), 0).
It means add the number of months since 1/1/1900 to 1/1/1900, giving me the first day of this month.
Use dd for days and you strip off time.
Use y for years and you strip it down to January first.
Adjust 0 in the datadd piece as necessary for other days.
DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
Find the difference in months since 1/1/1900 and today. Remove 4 months. Add that number of months to 1/3/1900.
You should be ending up on the 3rd of the month, four months ago, not the first. Can you double check your result?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 4:14 am
Craig, you've got me confused now. What part of that equation changes the days from the 1st to the 3rd?
February 1, 2011 at 11:56 am
DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
When I run this query on 29th January it gaves me 10/01/2010.
When I run it right now on 02/01/2010 its giving me 11/01/2010.......
February 1, 2011 at 1:40 pm
Brandie Tarvin (2/1/2011)
Craig, you've got me confused now. What part of that equation changes the days from the 1st to the 3rd?
The +2 part at the end. Here, look at this:
select
DATEADD( m, 0, 0),
DATEADD( m, 0, 2),
DATEADD( m, 0, +2),
DATEADD( m, 1, +2)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 1:46 pm
npatel.bi (2/1/2011)
DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))When I run this query on 29th January it gaves me 10/01/2010.
When I run it right now on 02/01/2010 its giving me 11/01/2010.......
When I run this today (2/1/2011):
SELECTDATEADD( m, DATEDIFF( m, 0, getdate()) -4, 2)
I get:
2010-10-03 00:00:00.000
Now, going to SSIS, when I put this into a script object:
msgbox( CSTR( DATEADD("m", DATEDIFF("m", 0, Date)-4, 2)))
This gives me an error with Date:
Date is a type and cannot be used as an expression.
Swapping it to Today for VB, and I get other errors.
Where are you getting this from, what command object are these loaded into?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 2, 2011 at 4:38 am
Craig Farrell (2/1/2011)
Brandie Tarvin (2/1/2011)
Craig, you've got me confused now. What part of that equation changes the days from the 1st to the 3rd?The +2 part at the end. Here, look at this:
selectDATEADD( m, 0, 0),
DATEADD( m, 0, 2),
DATEADD( m, 0, +2),
DATEADD( m, 1, +2)
But doesn't "m" stand for month, not day? That's the part that's confusing me.
Odd, when I tried to test this in SSMS yesterday, it didn't like my zeroes. I wonder what I did differently than you, because yours works...
Hm. Npatel, I'd like to test this in SSIS Script Task, but I need your DIM statements from that script. Can you please post them?
February 2, 2011 at 4:39 am
Craig Farrell (2/1/2011)
npatel.bi (2/1/2011)
DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))When I run this query on 29th January it gaves me 10/01/2010.
When I run it right now on 02/01/2010 its giving me 11/01/2010.......
Now, going to SSIS, when I put this into a script object:
msgbox( CSTR( DATEADD("m", DATEDIFF("m", 0, Date)-4, 2)))
This gives me an error with Date:
Date is a type and cannot be used as an expression.
I'm having the same issues. This is T-SQL mixed up with .Net or VB or C#. How are you getting it to work, npatel.bi?
February 3, 2011 at 12:50 pm
Brandie Tarvin (2/2/2011)
Craig Farrell (2/1/2011)
Brandie Tarvin (2/1/2011)
Craig, you've got me confused now. What part of that equation changes the days from the 1st to the 3rd?The +2 part at the end. Here, look at this:
selectDATEADD( m, 0, 0),
DATEADD( m, 0, 2),
DATEADD( m, 0, +2),
DATEADD( m, 1, +2)
But doesn't "m" stand for month, not day? That's the part that's confusing me.
Odd, when I tried to test this in SSMS yesterday, it didn't like my zeroes. I wonder what I did differently than you, because yours works...
Hm. Npatel, I'd like to test this in SSIS Script Task, but I need your DIM statements from that script. Can you please post them?
Yep, it does. I think we've been goofing off with the numeric additions to datediff/add that we've obfuscated how it works, sorry.
Remember, Dateadd: 1st Argument: what to add, 2nd arg: how many of them to add, 3rd Arg: What we're adding it to.
So, in the third argument 0 = 1/1/1900. 2 = 1/3/1900. 1/3/1900 = 2 days after 1/1/1900.
So, If I add 3 months to 1/1/1900 I get 4/1/1900. If I add 3 months to 1/3/1900 I get 4/3/1900.
That's why, with his code, the +2 at the end starts on 1/3, and he should be getting the 3rd day of the month.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 5:08 am
Right. Okay. I get it.
Since the third argument is supposed to be date, but there's a "+2" in it instead, it's taking 01-01-1900 and adding 2 days.
Which is weird, odd, and non-intuitive. So, you're correct that he should be getting the third of the month, not the first. Which means we really need to see that code to figure out what else is going on.
All I can think of is that Date was declared as a variable and other stuff has been done to it either before or after this point in the code.
February 4, 2011 at 9:00 am
Here is the code:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim bDate, tDate ,StartDate, EndDate, ReportDate
StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +2))
EndDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -2, +2))
ReportDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date)-1, +1))
'Format Date Fields , must be in YYYY-MM-DD
Dim SYear,SMonth,SDay,EYear,EMonth,EDay,RYear,RMonth,RDay
Dim SString
SYear = Year(StartDate)
SMonth = Month(StartDate)
SDay = Day(StartDate)
SDay = RTrim(SDay)
If Len(Sday) = 1 Then
SDay = "0" + SDay
End If
SString = SYear & "-" & SMonth & "-" & SDay
DTSGlobalVariables("SDate").Value = SString
Dim EString
EYear = Year(EndDate)
EMonth = Month(EndDate)
EDay = Day(EndDate)
EDay = RTrim(EDay)
If Len(Eday) = 1 Then
EDay = "0" + EDay
End If
EString = EYear & "-" & EMonth & "-" & EDay
DTSGlobalVariables("EDate").Value = EString
Dim RString
RYear = Year(ReportDate)
RMonth = Month(ReportDate)
RDay = Day(ReportDate)
RDay = RTrim(RDay)
If Len(Rday) = 1 Then
RDay = "0" + RDay
End If
RString = RYear & "-" & RMonth & "-" & RDay
DTSGlobalVariables("RDate").Value = RString
Main = DTSTaskExecResult_Success
End Function
/**********************************/
I will be available to discuss on this on monday. I have too much work to do today....Sorry guys..Have a nice weekend and thanks for all your post...
February 4, 2011 at 12:12 pm
VBScript. I shoulda guessed. 😉
Is this a legacy DTS package stored on your 2k5 server?
Also, to the original question:
StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
EndDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +1))
ReportDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date)-2, +1))
This query gives the data from 10/01/2010 to 10/30/2010. It suppose to give me from 10/01/2009 to 10/31/2010. I am not getting that why. Can any one explain me in detail how these query pull the data as of now I have to pull data of all the months of 2009 and I am new to SQL Server.
None of this deals with a year. It's all month conversions and nothing over 12 (12 months equaling 1 year). This wasn't meant to do what you expect. Also, the code you put in above merely feeds some local package variables. I assume these are then used as parameters elsewhere?
I'm currently trying to locate an old server to see what the integers in the third position of dateadd result as. I have to test it to remember for VBScript. It obviously doesn't act the same.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 12:25 pm
I know you won't get back to this until next week, but I'm posting this before I forget. ActiveX tasks will be removed (or already have been removed) from SSIS in the very immediate future. I recommend deleting this task and replacing it with a Script Task or an Execute SQL Task.
To break down the code into something you'll understand:
dim bDate, tDate ,StartDate, EndDate, ReportDate
Dim statements are your variable declare statements. In T-SQL, the same word is "Declare".
StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +2))
Working from the inside out:
"DateDiff" statement is the interval between today (represented by Date) and '01/01/1900'. As of today, February 4, 2011, there are 1333 months since '01/01/1900'. That leaves the code to be:
StartDate = CStr(DATEADD("m", 1333 -3, +2))
--Subtract the middle number, equals 1330.
StartDate = CStr(DATEADD("m", 1330, +2))
"DateAdd" statement is: "Add 1330 months to ('01/01/1900' plus two days)." The value that results is
11/03/2010.
"CStr" statement is: "Change the date to a string."
"StartDate =" means set the StartDate variable to be '11/03/2010'.
'Format Date Fields , must be in YYYY-MM-DD
Dim SYear,SMonth,SDay,EYear,EMonth,EDay,RYear,RMonth,RDay --Another variable declaration
Dim SString --Another variable declaration
SYear = Year(StartDate) --Set this variable to the year of the StartDate variable
SMonth = Month(StartDate) --Set this variable to the month of the StartDate variable
SDay = Day(StartDate) --Set this variable to the day of the StartDate variable
--All of the above is pretty standard. SYear = 2011, SMonth = 11 and SDay = 3
SDay = RTrim(SDay) --Trim off the right side spaces
If Len(Sday) = 1 Then
SDay = "0" + SDay
End If --Add zero to single digit days.
--Interesting that the code doesn't account for, or care about, single digit months
SString = SYear & "-" & SMonth & "-" & SDay
--Set the string to equal 2011-11-03
DTSGlobalVariables("SDate").Value = SString
--Throw this string into an external variable for the package.
Use the same logic to interpret EndDate and ReportDate.
Like Craig, I find it fascinating that you're getting the correct information for your StartDate. I would much druther do this another way.
Using "Execute SQL Task"
--Make sure variables are set up in the Result Set tab
Declare @StartDate char(10), @EndDate char(10), @ReportDate char(10),
@MonthStart smalldatetime;
Set @MonthStart = DateAdd(m,-3,GetDate());
Set @StartDate = LEFT(Convert(char(24),@MonthStart,120),7) + '-01';
Set @EndDate = LEFT(Convert(char(24),DateAdd(dd,-1,
DateAdd(m,1,Convert(smalldatetime,@StartDate,120))),120),10);
Set @ReportDate = -- I will leave the setting of ReportDate up to you.
This gives you the rudiments of the first calendar day of the month and the last calendar day of the month. EndDate is figured by adding 1 month to the StartDate, then subtracting 1 day. If you wish to change these dates, look up DateAdd() and Convert() in Books Online for more details.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply