由买买提看人间百态

topics

全部话题 - 话题: datediff
1 (共1页)
O**K
发帖数: 11
1
来自主题: Database版 - sql question
select Name, Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) ) as A
union
select Name, Date + DateDiff as Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) )
e***t
发帖数: 1
2
Use DateDiff function for the first one:
datediff(hh, date1, date2)
use decimal or numeric for the second one, you can declare it as
decimal(10,2)
m*********y
发帖数: 389
3
Here are some questions I copied from online.. Obviously LouZhu is a lazy
ass... these questions are everywhere... :-)
SQL Interview questions
Below is a list of questions in this blog post so you can test your
knowledge without saying answers. If you would like to see questions and
answers please scrool down.
Question: What type of joins have you used?
Question: How can you combine two tables/views together? For instance one
table contains 100 rows and the other one contains 200 rows, have exac... 阅读全帖
t****n
发帖数: 10724
4
来自主题: Database版 - 来做sql题目。
借idtknow的思路,改进一下。比较不同行的日期,而不是同一行的日期。希望没有虫子
SELECT userID, StartDT, EndDT,
ROW_NUMBER() OVER (partition by userID order by StartDT) rk,
DATEDIFF(dd, StartDT, EndDT) diff
into #ttt
FROM #tbl
SELECT a.userID, a.rk, a.StartDT as A_Start, a.EndDT as A_End, b.StartDT as
B_Start, b.EndDT as B_End
into #shift
FROM #ttt a
LEFT JOIN #ttt b on a.userID = b.userID and a.rk + 1 = b.rk
order by a.userID, a.rk
select userID, min(A_Start)as StartDate, max(case when B_End IS null or
Cat= 'Y' then A_End else B_E... 阅读全帖
d******o
发帖数: 2489
5
来自主题: SanFrancisco版 - 请教如何给软件加个license (转载)
很多方法吧,从registry、系统文件里边,不过都会被破解。
如果你用C#,试一下:
// adding trial period for the application
// set for 30 days on initial launch
Option Explicit
Private Const TRIAL_PERIOD_DAYS As Integer = 30
Private Function TrialPeriodDaysLeft(DaysTrial As Integer) As Integer
Dim DateStart As Date
DateStart = GetSetting(App.Title, "Trial Period", "Date Start", 0)
If DateStart = 0 Then
SaveSetting App.Title, "Trial Period", "Date Start", Date
Else
TrialPeriodDaysLeft = DateDiff("d", DateStart, Date) > DaysTri... 阅读全帖
c**g
发帖数: 274
6
来自主题: Database版 - Re: A question for SQL Server
I have no idea if this is the best. When I work on sql, I always have
a reference on my desk. This kind of interview quesion silly
(my personal view). A good memory of manual is not that useful in
real work.
in t-sql
update A
set Value = B.Value
from A, B
where datediff(mi, A.dateTime, B.dateTime) <= 1
h*****l
发帖数: 184
7
datediff()
C****n
发帖数: 2324
8
来自主题: Database版 - Help on SQL statement
Yea, this is one time thing.
You mean to write a program t od this? It's really over kill. A CPA won't
understand what you are talk about, and she won't know what to do.
a SQL is the simplest solution cause she can just copy and paste and got wgat
she wants.
Here is the SQL I worked out, no joins. :-)
select ProductID,
Max(IIF(Date > "1999-09-30" and Date <="2000-09-30", DateDiff("D",Date,
"1999-09-30") * 100000 + price, 0)) Mod 100000 as LatestPriceOf2000,
Max(IIF(Date > "2000-09-30" and Date <
z***y
发帖数: 7151
9
select t.[text] as query_last3hours from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (s.sql_handle) t
where datediff(hh, last_execution_time , getdate())<1
g***l
发帖数: 18555
10
这个是最简单的
select phonenum,
DateDiff(dd, '12/31/2009',phonedate)
as DayNumber,
1 as PhoneCount
into PhoneCountTemp
from table
select phonenum,DayNumber, sum(phonecount) as PhoneCount
from PhoneCountTemp
group by phonenum, daynumber
order by phonenum, daynumber
g***l
发帖数: 18555
11
我改了,如果把2/1/2010当第一天,就是DATEDIFF(DD,'1/31/2010',PHONEDATE)
n********6
发帖数: 1511
12
LOGIC:
Today = DateDiff("d", "01/01/1900", Date())
CurrentWeekDay = (Today Mod 7) + 1
FirstWeekDay = DateAdd("d", Today-CurrentWeekDay, "01/01/1900")
LastWeekDay = DateAdd("d", Today+6-CurrentWeekDay, "01/01/1900")
SELECT * FROM TABLEA
WHERE TRANSACTIONDATE >= FIRSTWEEKDAY
AND TRANSACTIONDATE <= LASTWEEKDAY
/* works in asp */
Any simple method?
s**********o
发帖数: 14359
13
来自主题: Database版 - How to write this loop SQL query?
有DATEDIFF(),然后取DAY
k*****n
发帖数: 361
14
来自主题: Database版 - How to write this loop SQL query?
我知道datediff的,但是条件是除去date range在FR里的
c*********c
发帖数: 113
15
一张table里有【start time】和 【end time】,类型是date
需要计算时间间隔,以小时计,datediff貌似不行,纠结半天了。。。
s**********o
发帖数: 14359
16
SELECT DATEDIFF(hour,'2008-06-05','2008-08-05')
i*****w
发帖数: 75
17
来自主题: Database版 - 来做sql题目。
Try this:
DECLARE @tbl Table (userID int , StartDT datetime, EndDT datetime)
INSERT INTO @tbl (userID, StartDT, EndDT)
Select 1, '12/02/2011', '01/16/2012'
UNION ALL
Select 1, '03/04/2012', '03/24/2012'
UNION ALL
Select 1, '04/05/2012', '04/26/2012'
UNION ALL
Select 1, '05/14/2012', '06/07/2012'
UNION ALL
Select 2, '03/05/2012', '03/30/2012'
UNION ALL
Select 2, '08/04/2012', '09/15/2012'
UNION ALL
Select 2, '04/01/2012', '04/30/2012'
UNION ALL
Select 3, ... 阅读全帖
o***i
发帖数: 603
18
来自主题: Database版 - 来做sql题目。
你开始的DATEDIFF(dd, startDT, EndDT) diff这里
感觉不用cte不成的
s**********o
发帖数: 14359
19
来自主题: Database版 - 来做sql题目。
而且是ORDER好的吧,其实30天肯定要DATEDIFF了,否则没法控制2月的28,9天
y****w
发帖数: 3747
20
来自主题: Database版 - 来做sql题目。
好吧,我看前面人家用datediff了。
w****w
发帖数: 521
21
来自主题: Database版 - 来做sql题目。
把老祖宗解答放这里压阵,不精版理不容啊。
把同组的EndDate都设成同一个,再挑最小的StartDate。
--------------------------------------
select UserId,min(StartDate) as StartDate, EndDate
from
(select UserId,StartDate,
(select min(EndDate)
from work_time B
where A.UserId=B.UserId and A.StartDate<=B.StartDate
and not exists
(select *
from work_time C where B.UserId=C.UserId and B.StartDate StartDate
and datediff(day,B.EndDate,C.StartDate)<30)
... 阅读全帖
s**********o
发帖数: 14359
22
来自主题: Database版 - 考题:SSIS比较文件日期和LOADDATE
其实是这样,如果EXCEL FILE的 LAST MODIFIED DATE和上一次FILE LOADDATE的时间不
一致,
就重新LOAD EXCEL FILE到SQL SERVER里,SSIS把EXCEL FILE的 LAST MODIFIED DATE读
到PACKAGE
VARIABLE里了,怎么跟TABLE里记录的LOADDATE比啊,好像是用DATEDIFF,要注意什么
s**********o
发帖数: 14359
23
来自主题: Database版 - 考题:SSIS比较文件日期和LOADDATE
最后解决了,把VARIABLE传到SQL里去DATEDIFF
然后传出来到一个变量,然后用个CONDITION FX
就可以了,SSIS的DATETIME是个DATE,搞了半天
w****w
发帖数: 521
24
select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖
w****w
发帖数: 521
25
select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖
v******n
发帖数: 421
26
来自主题: Linux版 - 用Word写日记的问题
估计得写宏了,查了下vba,有DATEDIFF这个
n*w
发帖数: 3393
27
来自主题: Programming版 - metaprogramming
比如
- case when b>d and c<1000 then 1 else 0 end
- datediff(m, x, y) < 30
这些都是有效的sql syntax,希望这些规则可以在database那边也可以直接用。
w****w
发帖数: 521
28
来自主题: Programming版 - 求最佳解法
不让贴sql code?
s elect * f rom
(
s elect a.ID,a.Date,
(
s elect sum(b.Amount)
f rom Transactions b
w here a.ID=b.ID and a.Date<=b.Date
and datediff(day,a.Date,b.Date)<365
) as OneYearTotal
f rom Transactions a
)
w here OneYearTotal>100
w****w
发帖数: 521
29
来自主题: Programming版 - 求最佳解法
下面程序产生22m transactions,8分钟。
import sys
from datetime import date,timedelta
from random import randint
ID_NUM=100000
DAY_NUM=3650
StartDate=date(2004,1,1)
fmt="%m/%d/%Y"
with open(sys.argv[1],"w") as fo:
fo.write("IDtDatetAmountn")
for i in xrange(22000000):
RandonId=randint(1,ID_NUM)
RandonDate=StartDate+ timedelta(days=randint(0,DAY_NUM))
RandonAmt=5*randint(1,20)
fo.write("%dt%st%dn" % (RandonId,RandonDate.strftime(fmt),RandonAmt))
下面程序产生id,80分钟
import s... 阅读全帖
n*********n
发帖数: 5605
30
来自主题: Statistics版 - 问一个R的关于date/time的问题
分别把把x,y covert成POSIXct class后
有什么类似别的语言里的datediff()的fucntion可以
算y,x的interval的function吗?
谢谢
1 (共1页)