Showing posts with label guysi. Show all posts
Showing posts with label guysi. Show all posts

Monday, March 19, 2012

Date headache

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

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