Iterate over all values in table and check if any value is of type datetime and then change its format

Question

I have some dynamic columns created inside a SQL Server table, and I want to know which dynamic column has a datetime type value.

I got all data in a datatable and then did select * from datatable;
but now I don't know how to iterate over every value to know which is of datetime type.

[For the 1024 time – DateTime has no format!](https://zoharpeled.wordpress.com/2019/12/19/for-the-1024-time-datetime-has-no-format/) — Zohar Peled, Jul 15 '20 at 05:14
Are you asking which columns have the `datetime` data type? Or are you asking which columns have string values that look like `datetime`s? If the latter, provide sample data and desired results. Also, what is a "dynamic column"? — Gordon Linoff, Jul 15 '20 at 12:17

score 3 · Answer 1 · answered Jul 15 '20 at 05:50

3

Assuming that you have the data loaded into the data table you may check if the datatype of the column is date or not via the following command:

Select 
isDate(columnName)
from datatable;

You can do it on multiple columns as well. The above code is just an example on how to use isDate to check if the column values are date format or not.

answered Jul 15 '20 at 05:50

Azher Aleem

738
6
15

1

Good suggestion. There is one small caveat with `ISDATE`, as it returns `0` for datetime2 datatype. – Venkataraman R Jul 15 '20 at 06:01
No, `IsDate` checks if a string expression can be converted to a DateTime, it doesn't check the data type of the expression itself. – Zohar Peled Jul 15 '20 at 06:36
@ZoharPeled It checks if it is a valid date and assuming that user wants to check data type based on the values in the column. – Azher Aleem Jul 15 '20 at 07:07
@VenkataramanR Agreed! This link might be more helpful for datetime2: https://stackoverflow.com/questions/11043981/is-there-a-function-like-isdate-for-datetime2 – Azher Aleem Jul 15 '20 at 07:17
@AzherAleem `IsDate` is a problematic suggestion at best, and it also doesn't answer the question being asked here. First, it doesn't say anything about the actual data type (which is what the OP asked for), second, it's non-deterministic (most of the times), third, it doesn't play well with the more modern data type of `datetime2`, and for many more reasons (read [official documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-ver15)) – Zohar Peled Jul 15 '20 at 07:35
@ZoharPeled Agreed for datetime2 datatype. Thanks! – Azher Aleem Jul 15 '20 at 07:51

score 3 · Answer 2 · answered Jul 15 '20 at 05:51

you can use system catalog to find out the types.

CREATE TABLE test(a int, b datetime)

SELECT c.name,ty.name 
FROM sys.columns as c
join sys.tables as t
on c.object_id = t.object_id
join sys.types as ty
on ty.system_type_id = c.system_type_id
where t.name = 'test'


+------+----------+
| name |   name   |
+------+----------+
| a    | int      |
| b    | datetime |
+------+----------+

Iterate over all values in table and check if any value is of type datetime and then change its format

2 Answers2