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
1
  • 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? Commented Jul 23, 2015 at 1:22

2 Answers 2

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.

4
  • 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
    Commented Jul 22, 2015 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. Commented Jul 22, 2015 at 23:11
  • 1
    Seems odd to me to assume that all dates are American unless they have a month > 12? Commented Jul 23, 2015 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... Commented Jul 23, 2015 at 6:15
2

How about this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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