March 9, 2010 at 10:06 am
Hi:
I am a SQL learner and i just wrote my first stored proc,i am trying to round the data in a column calle _Addwhen to nearest 15 minutes.
eg:
_addwhen Normalized)addwhen
9:41:3000 ------------> 9:45
I am very close,my code rounds to the enarest 10 minutes.but iam not able to round to 15 minutes,i know it is this line i need to change but am not getting the answer:
cast(round(DATEPART(Mi,[_Addwhen]),-1 --> rounds to nearest 10 minutes
I already tried this:
cast(round([_Addwhen]*15,0)/15
but it does not work i get errors:
can someone plz advise?
Regards
March 9, 2010 at 11:37 am
Could you please define your rounding rulles? When to round up and when to round down.
March 9, 2010 at 11:42 am
Hi:
It would be to the nearest 15th minute handle
eg :
9:33:3000 ----------------> 9:30
9:38:3000 ----------------->9:45
9:42:3000 ----------------->9:45
hope that is clear,but is my code not correct in its logic?
March 9, 2010 at 11:57 am
Does the following code help you?
declare @TestTime datetime;
set @TestTime = convert(datetime, '0:14:00', 108);
select @TestTime, dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, @TestTime), 0), @TestTime) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, @TestTime), 0));
set @TestTime = convert(datetime, '9:33:30', 108);
select @TestTime, dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, @TestTime), 0), @TestTime) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, @TestTime), 0));
set @TestTime = convert(datetime, '9:37:30', 108);
select @TestTime, dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, @TestTime), 0), @TestTime) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, @TestTime), 0));
set @TestTime = convert(datetime, '9:38:30', 108);
select @TestTime, dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, @TestTime), 0), @TestTime) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, @TestTime), 0));
set @TestTime = convert(datetime, '9:41:30', 108);
select @TestTime, dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, @TestTime), 0), @TestTime) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, @TestTime), 0));
March 9, 2010 at 12:02 pm
Thanks Lynn,your code works perfectly but how do I use it in my current code:
here is my set statement :
declare @sql nvarchar(500)
set @sql='update XXNkk_Fact_PnLSummary1 set [' + @colname + '] = cast(DATEPART(yyyy,[_Addwhen]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[_Addwhen]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[_Addwhen]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[_Addwhen]) as nvarchar(2)) + '':'' + cast(round(DATEPART(Mi,[_Addwhen]),-1) as nvarchar(2)) '
print @sql
exec(@sql)
March 9, 2010 at 12:10 pm
arun.prakash (3/9/2010)
Thanks Lynn,your code works perfectly but how do I use it in my current code:here is my set statement :
declare @ sql nvarchar(500)
set @sql='update XXNkk_Fact_PnLSummary1 set [' + @colname + '] = cast(DATEPART(yyyy,[_Addwhen]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[_Addwhen]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[_Addwhen]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[_Addwhen]) as nvarchar(2)) + '':'' + cast(round(DATEPART(Mi,[_Addwhen]),-1) as nvarchar(2)) '
print @sql
exec(@sql)
I have no idea based on the code above. I'd need to see the DDL (CREATE TABLE statement) for a table, some sample data (as a series of INSERT INTO statments) that would be representative of your problem.
March 9, 2010 at 12:12 pm
DDL below :
GO
CREATE TABLE [dbo].[XXNkk_Fact_PnLSummary1](
[AccountName] [nvarchar](255) NOT NULL,
[FundName] [nvarchar](255) NOT NULL,
[PercentageYTD] [decimal](18, 2) NULL,
[DollarYTD] [money] NULL,
[PercentageMTD] [decimal](18, 6) NULL,
[DollarMTD] [money] NULL,
[PercentageDayChange] [decimal](18, 6) NULL,
[SummaryDate] [datetime] NULL,
[_Addwhen] [datetime] NULL,
[_AddBy] [nvarchar](255) NULL,
[_AddBySystem] [nvarchar](255) NULL,
[_AddWhenMostRecentRcdInd] [int] NULL,
[Normalized_Add_When] [datetime] NULL
) ON [PRIMARY]
GO
March 9, 2010 at 12:18 pm
insert into XXNkk_Fact_PnLSummary1 values ('Global Dynamic Asset Allocation', 'MTD','LPLUS','NULL','NULL',-0.000414,-326206.9786,-0.002742,2010-02-18 16:06:03.000,2010-03-09 13:26:30.000,a21249,Feb GDAA Summary.xls,NULL,2010-03-09 13:30:00.000)
March 9, 2010 at 12:23 pm
Did you test the code you posted on your system. Things don't look right once I reformat the code.
March 9, 2010 at 12:24 pm
Also, what column should be updated and what should it look like when completed?
March 9, 2010 at 12:27 pm
It seems to work perfectly in my computer,here is the full procedure
USE [Cortex]
GO
/****** Object: StoredProcedure [dbo].[SPNormalizeAddWhen1] Script Date: 03/09/2010 14:26:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPNormalizeAddWhen1]
As
declare @colname nvarchar(20)
set @colname = 'Normalized_Add_When'
if not exists (select * from syscolumns where id=object_id('XXNkk_Fact_PnLSummary1') and name=@colname)
exec('alter table XXNkk_Fact_PnLSummary1 add [' + @colname + '] datetime')
declare @sql nvarchar(500)
set @sql='update XXNkk_Fact_PnLSummary1 set [' + @colname + '] = cast(DATEPART(yyyy,[_Addwhen]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[_Addwhen]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[_Addwhen]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[_Addwhen]) as nvarchar(2)) + '':'' + cast(round(DATEPART(Mi,[_Addwhen]),-1) as nvarchar(2)) '
print @sql
exec(@sql)
March 9, 2010 at 12:29 pm
Well, your insert statement isn't working on my server.
March 9, 2010 at 12:37 pm
I am only interested in 2 columns here :
_Addwhen - format is 20100303 9:41:3000
Normalized_Add_When - format should be format is 20100303 9:45 (after I run my stored procedure)
The other columns dont really matter,you can enter whatever values u like in them.
Thanks for the help
March 9, 2010 at 12:52 pm
arun.prakash (3/9/2010)
I am only interested in 2 columns here :_Addwhen - format is 20100303 9:41:3000
Normalized_Add_When - format should be format is 20100303 9:45 (after I run my stored procedure)
The other columns dont really matter,you can enter whatever values u like in them.
Thanks for the help
Have to ask, if you aren't willing to provide working test code, why should we even try to help? This is your problem, help us help you solve it.
So, using a quick test table in my sandbox database I hope this code helps you.
create table dbo.MyTest (
StartDate datetime,
RoundedDate datetime null
);
insert into dbo.MyTest(StartDate)
select '2010-03-16 14:13:23.000';
select
StartDate,
dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, StartDate), 0), StartDate) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, StartDate), 0))
from
dbo.MyTest;
update dbo.MyTest set
RoundedDate = dateadd(ss,((datediff(ss, dateadd(dd, datediff(dd, 0, StartDate), 0), StartDate) + 450) / 900) * 900, dateadd(dd,datediff(dd, 0, StartDate), 0))
select
*
from
dbo.MyTest;
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply