2

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic:

  • Only add a value to 'ToDateTime' on rows where AID equals 123 and PID equals 2. (rows 2, 4, 5, and 7 below) - otherwise leave 'ToDateTime' as NULL.

  • If the above condition is met, set/leave 'ToDateTime' as NULL if the data (varchar) cannot be converted to datetime (rows 5 and 7 below have data that will fail datetime conversion)

  • But if it does contain a value that can be converted to datetime (rows 2 and 4), convert it and update the value in ToDateTime with that converted value.

BEFORE:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | NULL
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | NULL
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

AFTER:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | 2015-07-21 08:15:06.000
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | 2015-07-19 12:29:42.000
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

I have been trying a query similar to what is below but it is filling ToDateTime with the same values for every row (in the case of my actual data, NULL.) I'm not sure if something similar to this query is the right approach, or if I'm way off.

UPDATE MyTable SET ToDateTime =
CASE
WHEN ISDATE(myT.cd)= 1 
THEN CONVERT(DATETIME, myT.cd)
ELSE NULL END
FROM (
 SELECT ContainedData cd
 FROM MyTable
 WHERE [AID] = 123 AND [PID] = 2
 ) myT
  • 3
    If one of the values were 7/8/2015 8:15:06 AM, how would I know if that's July 8 or August 7? – Aaron Bertrand Jul 23 '15 at 1:22
2

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...

something like this, which uses the language setting to control isdate and convert.

set language english;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

set language british;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.

  • The table is huge and I don't have a clear way of identifying the date formats the times are appearing. It can be any time format from around the globe. Out of 100,000 rows, 15,000 of them are NULL or otherwise cannot be converted to datetime. – Krondorian Jul 22 '15 at 23:05
  • Simple enough to script out the update for each region/language that could be in there I would have thought. And 100,000 rows is not big, so one update per language would be ok. – Mister Magoo Jul 22 '15 at 23:11
  • 1
    Seems odd to me to assume that all dates are American unless they have a month > 12? – Aaron Bertrand Jul 23 '15 at 1:25
  • Aaron, there appears to be nothing in the data to indicate which region the dates belong to, and I think I used the word possibly, so I would expect that Krondorian would take a more informed view on that. There are also problems with low numbered days and months which would be valid either way... – Mister Magoo Jul 23 '15 at 6:15
2

How about this:

UPDATE [dbo].[MyTable]
SET [ToDateTime] = [ContainedData]
WHERE aid = 123 
and pid = 2 
and isdate([ContainedData] ) = 1
  • I have to have the conversion of varchar data into datetime in order to do datetime comparisons with other queries – Krondorian Jul 22 '15 at 23:01
-3
UPDATE TABLE_NAME
SET ToDateTime = ContainedData
WHERE AID = 123 AND PID = 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.