How to generate the date

0
votes

Using SQL 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.

2
sqlsql-serversql-server-2005
Recommendation: if you have a date, make it a DATETIME column! You'll be much better off than having a varchar field.... – marc_s
save the 180000 to this 18:00:00 then you can convert it to a time value and compare and then add one to the day using a case statement – Andrew

2 Answers

2
votes

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
0
votes

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]