How to update and filter column values in SQL database

Help Studio
, ,
1

Hi,

I have table in SQL as below -

Column Name - Date
Column value - 25/04/2024 16:44 European/London , 26/04/2024 16:44 European/paris , 20/04/2024 16:44 European/London .

Here, I want to change column values to valid date format and filter to have data only for last 3 months using SQL query.

Any suggestion would be helpful,
Thanks in Advance

2

Hi @Hemasai06 you have not provided much detail but since this is asked in UiPath Forum I would assume that you will use the SQL data in UiPath. You can get your SQL table into a datatable then just use “For each row in datatable to “reformat” the date column. Inside for each row, just use an assign
CurrentRow(“Date”) = CurrentRow(“Date”).ToString.Split(” ")(0)

This will overwrite values under the date column with only the fist date part which is dd/MM/yyyy

3

Hi @avejr748 ,

Thank you for reply,

Is there any way we can do this using SQL query instead of for each?

4

I am not an expert in SQL but I would say this would be way easier to implement in Studio than do complex queries in SQL.

5

Hi,
Update the Date Format: Convert the dates to a valid SQL date format using the CONVERT function, assuming the dates are stored as text:
UPDATE your_table
SET Date = CONVERT(DATETIME, SUBSTRING(Date, 1, 16), 103)
WHERE ISDATE(SUBSTRING(Date, 1, 16)) = 1;

Filter for the Last 3 Months: Use a SELECT statement with a date filter to get records from the last three months:

*SELECT **
FROM your_table
WHERE DATEDIFF(month, CONVERT(DATETIME, SUBSTRING(Date, 1, 16), 103), GETDATE()) <= 3;

Note: Replace your_table with your actual table name and adjust the CONVERT style as needed for your date format.

1 Like