Using SQ开发者_开发百科L Server 2005
Tabl1
ID Date Intime Outtime
001 20101201 080000 180000
001 20101202 210000 060000
001 20101203 180000 090000
001 20101204 100000 170000
....
.
Date Datatype is nvarchar
Intime, Outtime datatype is nvarchar
Intime, Outtime Format: HHMMSS
From the table1, i want to make one more column name as NextDate. The Next Date Column should display the next date of Date Column with the following condition
If Outtime is Greater than Intime then it should display the next date
If outtime is less than intime then it should display the same date
Expected Output
ID Date Intime Outtime NextDate
001 20101201 080000 180000 20101201 (Outtime is less than Intime)
001 20101202 210000 060000 20101203 (Outtime is greater than Intime)
001 20101203 180000 090000 20101204 (Outtime is greater than Intime)
001 20101204 100000 170000 20101204 (Outtime is less than Intime)
....
How to make a query for the above condition.
Need Query Help.
I agree with @marc_s, when you work with date and time best way is use datetime datatype.
But you can use this code temporary.
SELECT ID, [Date], Intime, Outtime,
CASE
WHEN Outtime > Intime THEN
[Date]
WHEN Outtime < Intime THEN
CONVERT(
nvarchar,
DATEADD(
dd,
1,
CONVERT(
datetime,
SUBSTRING([Date],1,4)+'-'+SUBSTRING([Date],5,2)+'-'+SUBSTRING([Date],7,2)
)
),
112
)
END AS NewDate
Use Select Case:
DECLARE @Dates Datetime
DECLARE @Intime Datetime
DECLARE @Outtime Datetime
Set @Intime='1/21/2010'
Set @Outtime='1/20/2010'
Set @Dates=GETDATE()
Select @Dates,@Intime,@Outtime,
cASE
WHEN @Outtime > @Intime then @Dates + 1
WHEN @Outtime < @Intime then @Dates
ELSE GETDATE()
END as [NextDate]
精彩评论