CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Change Value on SQL View

I have the following table:

ID     myDate       myTime     Value
1      2014-06-01   00:00:00   100
2      2014-06-01   01:00:00   125
3      2014-06-01   02:00:00   132
4      2014-06-01   03:00:00   139
5      2014-06-01   04:00:00   145
6      2014-06-01   05:00:00   148
FF.
24     2014-06-01   23:00:00   205
25     2014-06-02   00:00:00   209
26     2014-06-02   01:00:00   215
27     2014-06-02   02:00:00   223

Then I have the following SQL Statement:

SELECT * FROM MyTable WHERE myDate = '2014-06-01'
UNION ALL
SELECT * FROM MyTable WHERE myDate = DATEADD(dd, 1, '2014-06-01') AND myTime = '00:00:00'

So the result should be from record number 1 to record number 25. What I am trying to do is myTime value with 00:00:00 on the last record, need to change to 24:00:00.

Does anyone know how to do this? Or is not possible?

Thank you.

Upvotes: 1

Views: 1398

Answers (3)

masum7
masum7

Reputation: 832

You can use simple IIF function of SQL Server: http://msdn.microsoft.com/en-au/library/hh213574.aspx

So the query will be like the below

SELECT 
ID, 
myDate, 
IIF(myTime='00:00:00', '24:00:00', myTime) as myTime 
FROM MyTable WHERE myDate = '2014-06-01'

Please mark as answer if it answers your question

Update: If it is sql server 2008 or earlier you can use case.. when

    SELECT ID,
            myDate,
            case when CAST(myTime AS VARCHAR(8))='00:00:00' then '24:00:00'
            else CAST(myTime AS VARCHAR(8))
            end as myTimeTemp 
            FROM MyTable WHERE myDate = '2014-06-01'
    UNION ALL
    SELECT ID,
            myDate,
            case when CAST(myTime AS VARCHAR(8))='00:00:00' then '24:00:00'
            else CAST(myTime AS VARCHAR(8))
            end as myTimeTemp  
    FROM MyTable WHERE myDate = DATEADD(dd, 1, '2014-06-01') AND myTime = '00:00:00'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You would do this with an or:

SELECT *
FROM MyTable
WHERE myDate = '2014-06-01' OR
      (myDate = DATEADD(dd, 1, '2014-06-01') AND myTime = '00:00:00');

EDIT:

SELECT date('2014-06-01') as mydate,
       (case when mydate <> date('2014-06-01') then '24:00:00' else mytime end) as mytime,
       REST OF COLUMNS HERE
FROM MyTable
WHERE myDate = '2014-06-01' OR
      (myDate = DATEADD(dd, 1, '2014-06-01') AND myTime = '00:00:00');

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

A couple comments. First, any reason you're storing the date and time in different columns? Seems like that could be simplified to store in a single column.

Regardless, no need for UNION ALL in this case, a simple OR statement would return the results you're looking for.

To answer you're question, if you want to update the last results from 00:00 to 24:00, something like this should do the trick without the need for OR or UNION:

UPDATE MyTable
SET mytime = '24:00:00'
WHERE myDate = DATEADD(dd, 1, '2014-06-01') AND myTime = '00:00:00'

No need for DATEADD if you're hard coding the date (just use '2014-06-02'), but I assume it's a passed in variable.

Edit, given your comments, this should work using a CASE statement:

SELECT ID, 
       myDate, 
       CASE WHEN myDate = DATEADD(day, 1, '2014-06-01') 
            THEN DATEADD(hour, 24, myTime)
            ELSE myTime
       End newMyTime,
       Value
FROM MyTable
WHERE mydate = '2014-06-01' OR 
     (myDate = DATEADD(day, 1, '2014-06-01') AND myTime = '00:00:00')

Upvotes: 0

Related Questions