g***l
发帖数: 18555 | 1 首先要有一个FUNCTION判断是不是BUSINESS DATE，别忘了HOLIDAY
create function [dbo].[udf_is_valid_business_date]
(
@date datetime
)
returns int
as
begin
declare
@is_holiday int,
@is_valid_business_date int
select @is_holiday =
case
-- memorial day (last mon of may)
when datepart(yy,@date) = 2009 and datepart(mm,@date) = 5 and
datepart(dd,@date) = 25 then 1
when datepart(yy,@date) = 2010 and datepart(mm,@date) = 5 and
datepart(dd,@... 阅读全帖 |
|
u*********e
发帖数: 9616 | 2 gejkl,
Thank you very much for helping me answering my question. I was doing
research myself and figuring out the issue. You are right. I rewrite the
query to get rid of cursor and use PATH XML instead.
One interesting thing I observed is that after I updated my sp and run the
report, it gave out an error msg:
"An error occurred during local report processing.Exception has been thrown
by the target of an invocation. String was not recognized as a valid
DateTime.Couldn't store <> in Stmt_Date_Cre... 阅读全帖 |
|
u*********e
发帖数: 9616 | 3 Thanks for the feedback.
I cleaned up the variables and use smalldatetime instead of nvarchar for the
comparison.
ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES]
@Statement_Year SMALLINT,
@Statement_Month TINYINT
AS
BEGIN
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarch... 阅读全帖 |
|
y****9
发帖数: 144 | 4 Googled and tested for a while, comp up with this method:
select cast(datepart(yy, getdate()) as varchar) + '-' +
cast(datepart(mm, getdate()) as varchar) + '-' +
cast(datepart(dd, getdate()) as varchar) + ' ' +
cast(datepart(hh, getdate()) as varchar)
Not sure this is the best idea in SQL Server. in Oracle it is easy:
select to_char('YY-MM-DD HH24', sysdate) from dual;
any suggestion? I am working on a query which need this conversion
Thanks, |
|
b******g
发帖数: 3883 | 5 http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bda433a
Steps:
1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.
2. Create a package level variable "XLFileRootDir" as string and set it to
the root
directory where you want the excel file to be created.
Example: C:\Project\Data\
3. Create an Excel connection in the connection manager. Browse to the
target directory
and select the destination XL... 阅读全帖 |
|
i*****w
发帖数: 75 | 6 Try this:
--Last Week means SUNDAY - SATURDAY
declare @tbl table (name varchar(30), dt datetime )
insert into @tbl values('Apple', '2011/11/1')
insert into @tbl values('Apple', '2011/11/2')
insert into @tbl values('Apple', '2011/11/4')
insert into @tbl values('Orange', '2011/11/10')
insert into @tbl values('Orange', '2011/11/5')
insert into @tbl values('Apple', '2011/11/6')
insert into @tbl values('Banana', '2011/11/10')
SELECT NAME, COUNT(*) as Total_Count, SUM(case when dt between DATEADD(dd, ... 阅读全帖 |
|
w********9
发帖数: 461 | 7 我抄了个function，就是计算n天以后的working day。现在有个holiday table tbl_
holiday，要把holiday排除在外，怎么加进去？
谢谢！
ALTER FUNCTION [dbo].[find_WorkingDayADD](
@date datetime, --start day
@workday int --days to be added
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--不是整周
SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
-- 整周
WHILE @workday<>0
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (... 阅读全帖 |
|
m******u
发帖数: 12400 | 8 这第二题。
我用cast(rand(datepart(millisecond, getdate())) as integer)
会出现许多同号的，而且，随机性并不好。比如我连续执行，出现的都是71,72,73什么
的，就是说，在一个很短的时间段内执行，结果也集中在某个值附近。
E.8.1
Create a batch that inserts 3000 rows in the employee table. The values of
the emp_no
column should be unique and between 1 and 3000. All values of the columns
emp_
lname, emp_fname, and dept_no should be set to 'Jane', ' Smith', and ' d1',
respectively.
E.8.2
Modify the batch E.8.1 so that the values of the emp_no column should be
generated
randomly us... 阅读全帖 |
|
l*******s
发帖数: 437 | 9 %let startdate = 01-sep-09;
%let enddate = 31-aug-10;
PROC SQL;
connect to &co.;
CREATE table sc as
select *
from connection to oracle
(select *
from ops$dardev.sc_ref s
where start_date >= '01-jan-08' and start_date <= '31-aug-10'
and spec_dep_name in ('CA')
)
;
quit;
我试着用 where "&startdate."d <= datepart(start_date) <= "&enddate."d， 不
成功，但在这个程序中有其他地方用到这两个macro，例如：
proc means data=CA
(where=("&startdate."d <= datepart(start_date) <= "&enddate."d))
都是成功的。不知道问题出在哪？查了sta... 阅读全帖 |
|
a9
发帖数: 21638 | 10 把9换成DATEPART(ww,GETDATE()) |
|
a9
发帖数: 21638 | 11 把getdate()替换成类似于
dateadd(dd,1- datepart(dw,convert(datetime,convert(varchar(4),getdate(),120)
+ '-01-01')),getdate()) |
|
m*********u
发帖数: 1491 | 12 这是老土的学生们做出来的答案, 用的是SQL Server：
=========================================
use test1
go
if object_id('t1') is not null
drop table t1
create table t1(
saleDate smalldatetime,
sales money)
insert into t1 values('11/01/2012',110), ('12/01/2012', 200), ('01/03/2013',
120),('01/04/2014',300)
select Month, [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]
from (select datepart(month, saleDate) as Dmonth,
left(datename(M,saleDate), 3) as Month,
left(datename(dw,saleDate),3) as day,
sales... 阅读全帖 |
|
p********a
发帖数: 5352 | 13 ☆─────────────────────────────────────☆
zhangqian80 (sweet) 于 (Sun Jan 20 17:19:22 2008) 提到:
How to convert the time ,
1/11/2007 0:10 AM
5/31/2007 2:00 PM
12/12/2007 10:00 AM
12/12/2007 1:00 Am
6/1/2007 1:50 PM
to
200701110010
200705311400
200712121000
200712120100
200706011350
Thank you!!!!!!!!
☆─────────────────────────────────────☆
statcompute (statcompute) 于 (Sun Jan 20 18:14:00 2008) 提到:
is your original data in datatime format? If yes, check following functions:
datepart(), tim |
|
|
d*******r
发帖数: 71 | 15 You may do as;
data a;
input x;
cards;
31536001
;
run;
data b;
set a;
day=put(datepart(x), mmddyyd10.);
time=put(timepart(x),time.);
run;
data c;
set b;
daytime=day||time;
run;
proc print data=c;
run;
format date20. also help you, not exact what you want,but you can read the
time as 31DEC1960:00:00:01.
proc print data=a;
format x datetime20.;
run; |
|
b*******r
发帖数: 152 | 16 your original data format is 'datetime'. use datepart() function first to
convert to 'date', then go from there.... |
|
d******9
发帖数: 404 | 17 "其中date1的格式是这样的 21MAY2001:00:00:00"
包括时间部分，应该用 datetime16.读入，然后把时间部分去除。
dateonly=datepart(date1); |
|
f*******i
发帖数: 8492 | 18 是这样的，我已经有一个datasheet，homework10.sas7bdat
然后，其中的date1格式就是“21MAY2001:00:00:00”， 但这个不是numeric value，
而是character
所以我根据楼上说的，先把date1读写为numeric value的date2，再从中提取date部分
data homework10;
set class.homework10;
date2=input(date1,date16.);
format date2 date16.;
date3=datepart(date2);
format date3 date9.;
range=date3-'01JAN2001'dt;
run;
proc print data=homework10;
var date1 date2 date3 range;
run;
如果时间格式是“21MAY2001:00:00:00”， 那么datetime后面跟着的数字是应该取15,
16,17，还是18？
我现在无论取那个数，得到的date2，都是这种格式“21MAY01:00:00:00”，并... 阅读全帖 |
|
q******d
发帖数: 158 | 19 use datepart(date1), then you can get the date1=21May2001 |
|
D******n
发帖数: 2836 | 20 create a .vim directory under you home directory(there is a dot before
vim)
and then create a syntax directory under it
and then create a sas.vim file under the syntax directory
==============sas.vim======================
if version < 600
syntax clear
elseif exists("b:current_syntax")
finish
endif
syn case ignore
syn region sasString start=+"+ skip=+\\|\"+ end=+"+
syn region sasString start=+'+ skip=+\\|\"+ end=+'+
" Want region from 'cards;' to ';' to be captured (Bob Heckel)
sy... 阅读全帖 |
|
p***7
发帖数: 535 | 21 现在我的date数据有两种形式: 10NOV05或者 10NOV05:03:49:19， 我需要不同的处
理: if date.......then date2=datepart(date); else if date.......then date2=
date; 省略号处如何写呢？Thanks!! |
|
