Guys
I have a table 1 row, a start and end date of a period
create table xperiod(startdate datetime , enddate datetime)
insert xperiod (startdate , enddate)
values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)
I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)
for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
and so on to
'2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'
if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
'2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
'2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'
and so on to
'2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'
Hope this makes sense !
I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
Thx in advance--eg:for one month period
select dateadd(dd,-1,dateadd(mm,1,getdate())) as endperiod
--eg:for 3 month period
select dateadd(dd,-1,dateadd(mm,3,getdate())) as endperiod
--eg:for 6 month period
select dateadd(dd,-1,dateadd(mm,6,getdate())) as endperiod|||That's perfect - thanks
Showing posts with label guysi. Show all posts
Showing posts with label guysi. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
Date field transfer problem!
Hello guys!
I have a problem transferring certain table columns
to excel with DTS. I have a colums where I have date
and time record like 02.10.03 14.45.33.
When I transfer this to excel, it loses the time from that
colums and has only 02.10.03.
What could be the problem?
Please help me,
JessicaYou should store the time in a separate column.|||Hi Jessica,
how is the exported field defined? TIMESTAMP?
And, which Task do you use for the export?
At this point I can imagine that the destination field in Excel has got a format like "DD.MM.YYYY" and not "DD.MM.YYYY hh.mm.ss", so that Excel just hides this information.
Perhaps I am wrong... It just should be a guess ;-)
Greetings,
Carsten
I have a problem transferring certain table columns
to excel with DTS. I have a colums where I have date
and time record like 02.10.03 14.45.33.
When I transfer this to excel, it loses the time from that
colums and has only 02.10.03.
What could be the problem?
Please help me,
JessicaYou should store the time in a separate column.|||Hi Jessica,
how is the exported field defined? TIMESTAMP?
And, which Task do you use for the export?
At this point I can imagine that the destination field in Excel has got a format like "DD.MM.YYYY" and not "DD.MM.YYYY hh.mm.ss", so that Excel just hides this information.
Perhaps I am wrong... It just should be a guess ;-)
Greetings,
Carsten
Subscribe to:
Posts (Atom)