-1

I have a table in SQL Server where sometimes the DATE column gets wrong date from import procedure and it gets set to 22th of Aug. 2099, while time part of DATE column is OK.

I can see all those columns with this query:

SELECT
    [ImportId], [Date], [PickupId],
    [Quantity], [Temp], [ImportDate]
FROM 
    [DatabaseName].[dbo].[Imports]
WHERE 
    Date  >= Convert(datetime, '2018-12-31' )

So, DATE column has wrong date and correct time, like here:

2099-08-22 03:10:18.000

And IMPORTDATE column has correct date, but zeroed out time part, like here:

2017-03-22 00:00:00.000

I would like to combine date part from IMPORTDATE and time part of DATE column, both together to become new DATE column:

2099-08-22 03:10:18.000    (time part from DATE column)
2017-03-22 00:00:00.000    (date part from IMPORTDATE column)
2017-03-22 03:10:18.000    (combined will be new DATE column)

So this record:

ImportId    Date                      PickupId  Quantity    Temp    ImportDate
74256       2099-08-22 03:10:18.000   111111    2356        3,8     2016-04-12 00:00:00.000

Would become this:

ImportId    Date                      PickupId  Quantity    Temp    ImportDate
74256       2016-04-12 03:10:18.000   111111    2356        3,8     2016-04-12 00:00:00.000

Anyone willing to construct proper SQL query to do this for me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Labsy
  • 19
  • 7
  • Be aware that this is not a site for free coding. You are expected to have done some research and you should attempt your own query, If you have problems ask for help showing us what you have tried. Expect downvotes if you simply ask for free code. – Paul Maxwell Mar 25 '17 at 00:03
  • Thank you for warning, but sometimes the task requires knowledge beyond my experience and I need to ask an expert for help. For example, in this case I have abandoned, non-maintained code to take care of and my basic knowledge of MySQL does not give me enough to even know where to start on this MSSQL code. But I will keep in mind for further participation. – Labsy Mar 27 '17 at 08:20

2 Answers2

0

Treat the incorrect datetime value as time only. Calculate the number of days from zero until the correct date. Add that number of days to the correct time = correct datetime value.

note you cannot use dateadd() on a time value, hence I have cast to time, then cast that back to datetime.

declare @d1 as datetime = '2099-08-22 03:10:18.000'
declare @d2 as datetime = '2017-03-22 00:00:00.000'

select
   cast(cast(@d1 as time) as datetime)
 , datediff(day,0,@d2)
 , dateadd(day, datediff(day,0,@d2), cast(cast(@d1 as time) as datetime))

+---------------------+-------+---------------------+
| 01.01.1900 03:10:18 | 42814 | 22.03.2017 03:10:18 |
+---------------------+-------+---------------------+
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

I can't comment here, but this is what you need: 

SELECT DATEADD(day, 0, DATEDIFF(day, 0, IMPORTDATE)) +
DATEADD(day, 0 - DATEDIFF(day, 0, DATE), DATE)
FROM [DatabaseName].[dbo].[Imports]
WHERE Date  >= Convert(datetime, '2018-12-31' )

You can find it here by LukeH: How to combine date from one field with time from another field - MS SQL Server

Community
  • 1
  • 1
mako
  • 78
  • 1
  • 6