April 8, 2009 at 9:20 am
I have a column like this:
2009-04-07 00:00:00.000
2009-04-06 00:00:00.000
2009-04-05 00:00:00.000
2009-04-04 00:00:00.000
2009-04-03 00:00:00.000
2009-04-02 00:00:00.000
2009-04-01 00:00:00.000
2009-03-31 00:00:00.000
2009-03-30 00:00:00.000
2009-03-29 00:00:00.000
I want to change the times for all these columns from 00:00:00.000 to 08:30:00.000.
I would appreciate if someone can let me know what would be the best way to achieve this?
Thank you!
April 8, 2009 at 9:24 am
Is that in a table, or a recordset variable, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 9:49 am
It is a table where I have this column as StartDate and I want to populate another table EndDate with the new values, I mean Date 00:00:00.000 to Date 08:30:00.000
April 8, 2009 at 10:11 am
The best way to do this is SSIS would be to write a T-SQL script to do the update and then use SSIS to execute the script.
April 8, 2009 at 11:09 am
I agree with you, but the simple UPDATE statement is not working here.
UPDATE dbo.Table1
SET StartDate = '08:30:00.000'
WHERE StartDate = '00:00:00.000';
This will not work as I have not mentioned dates at all in this query.
I want to keep the dates as they are, just change the time.
Can you please provide me the UPDATE statement that will work here.
Thank you!
April 8, 2009 at 11:11 am
insert into dbo.Table2 (EndDate)
select dateadd(minute, 30, dateadd(hour, 8, StartDate))
from dbo.Table1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 11:23 am
That worked perfect. But still one question.
Can you please tell me is it possible for me to get the StartDate from Table1 and insert the UpdatedStartDate as EndDate in Table1 itself, not in Table2.
April 8, 2009 at 11:28 am
Update dbo.Table1
set EndDate = dateadd(minute, 30, dateadd(hour, 8, StartDate))
where EndDate is null;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 11:34 am
GSquared (4/8/2009)
Update dbo.Table1set EndDate = dateadd(minute, 30, dateadd(hour, 8, StartDate))
where EndDate is null;
Worked perfect, Thank you so much!
April 8, 2009 at 11:45 am
When I am using SSIS, I am getting the results as following:
04/02/2009 12:00:00 AM
04/01/2009 12:00:00 AM
03/31/2009 12:00:00 AM
03/30/2009 12:00:00 AM
03/29/2009 12:00:00 AM
03/28/2009 12:00:00 AM
03/27/2009 12:00:00 AM
And when I am using SQL Server Mgmt Studio, I am getting the results as following:
2009-04-02 00:00:00.000
2009-04-01 00:00:00.000
2009-03-31 00:00:00.000
2009-03-30 00:00:00.000
2009-03-29 00:00:00.000
2009-03-28 00:00:00.000
I am running the same query in both the cases, but getting two different results in the table.
Can anyone help me with this? Why does it happen this way with SSIS? I want the results to pop up as it is doing in Mgmt Studio.
April 8, 2009 at 12:55 pm
I'm not sure what you mean. Are you looking at the data in SSIS? If so, how?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 1:46 pm
GSquared (4/8/2009)
I'm not sure what you mean. Are you looking at the data in SSIS? If so, how?
Yes. When I run the following query:
SELECT DISTINCT
DATEADD (dd,0, DATEDIFF(dd,0, call_time)) AS StartTime
FROM
DatabaseName.dbo.TableName
ORDER BY
DATEADD (dd,0, DATEDIFF(dd,0,call_time)) DESC
In SSIS, when I try to preview this query in the OLE DB Source, I am getting the following results
04/02/2009 12:00:00 AM
04/01/2009 12:00:00 AM
03/31/2009 12:00:00 AM
03/30/2009 12:00:00 AM
03/29/2009 12:00:00 AM
03/28/2009 12:00:00 AM
03/27/2009 12:00:00 AM
When I run the same above mentioned query in SQL Server Management Studio, I get the following results
2009-04-02 00:00:00.000
2009-04-01 00:00:00.000
2009-03-31 00:00:00.000
2009-03-30 00:00:00.000
2009-03-29 00:00:00.000
2009-03-28 00:00:00.000
If you observe, in both these results timings are different:
There is 12:00:00 AM in SSIS and 00:00:00.000 in Management Studio.
I want to get the results in SSIS exactly the same as I see it in Management Studio.
Hope you understand my question here. If still not, then I shall try to explain further.
Sorry about the confusion though!
April 8, 2009 at 1:47 pm
They're just displaying it differently. 00:00:00.000 is 12 AM (midnight). The difference doesn't matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply