CODEWITHSUNDEEP
sqlsql-serverdatetimesubquerydate-arithmetic
jcoke
jcoke

Reputation: 1891

Subquery returning more than 1 value - possible solution?

I am converting all the EPOCH/UNIX timestamp to a date time format which works in a normal SELECT statement when executed, however, in a subquery it's throwing an error because it is returning the converted date for each _id.

This seems like a straight forward solution but for some reason I can't get my head round it.

Any help will be much appreciated.

  SELECT 
        [id] = A._id
        ,[conId] = A.conId
        ,[createdAt] = ISNULL(CAST(CASE  
                        WHEN ([createdAt]) = '\N' THEN @defaultDate
                        WHEN ([createdAt]) = '' THEN @defaultDate
                        WHEN ([createdAt]) IS NULL THEN @defaultDate
                        ELSE (SELECT [createdAt] = DATEADD(MS, CAST(createdAt AS BIGINT)%(3600*24*1000), 
                       DATEADD(DAY, CAST(createdAt AS BIGINT)/(3600*24*1000), '1970-01-01 00:00:00.0')) FROM areas)
                    END AS DATETIME2(7)), @defaultDate)
        FROM areas A

Upvotes: 0

Views: 32

Answers (1)

GMB
GMB

Reputation: 222582

I don't see why you would need a subquery at all, when it seems like you just want to convert the epoch timestamp on the current row.

As for error handling, I would recommend try_convert rather than a case expression. Finally, you can assign the default value with coalesce().

select id, conid,
    coalesce(
        dateadd(second, try_convert(bigint, createdat), '19700101'), 
        @defaultdate
    ) as createdat
from areas

Upvotes: 1

Related Questions