| To partially Match cell against another partial cell [message #1773695] |
Tue, 03 October 2017 07:11  |
 Nagesh Kumar
Messages: 4
Registered: September 2017 |
Junior Member |
|
|
I have a problem with two tables which I need to compare dates for the same reference. The problem is that a) the dates are different formats and b) superfluous data is also contained within both date cells.
Global
No. Date
1 1992-08-25 00:00:00.000
2 2015-05-19 00:00:00.000
3 2000-01-12 00:00:00.000
Local
No. Date
1 25.08.1992 00:00:00.000000000 GMT
1 28.08.1992 00:00:00.000000000 GMT
2 19.05.2015 00:00:00.000000000 GMT
3 12.01.2000 00:00:00.000000000 GMT
3 17.01.2000 00:00:00.000000000 GMT
Note that the date formats between the two tables differ and contain lots of time and zero data that is not needed. So ideally I would like to see the format as DD-MM-YYYY.
I would like to return only the Global and Local entries where the Local date differs from Global date. So from the data above, I would want to see:
No. Date No. Date
1 25-08-1992 1 28-08-1992
3 12-01-2000 3 17-01-2000
I would put my attempts so far, but to be honest I have no idea on how to tackle the partial cell matching and re-formatting.
Any ideas?
Thanks
Nagesh Kumar https://mindmajix.com/sql-server-training
[Updated on: Tue, 03 October 2017 07:13] Report message to a moderator |
|
|
| Re: To partially Match cell against another partial cell [message #1857341 is a reply to message #1773695] |
Wed, 01 February 2023 04:59  |
|
Hi,
Here's one approach to solve this problem:
1. Parse the dates in both tables into JavaScript Date objects.
2. Compare the Date objects, and if they are not equal, store the corresponding Global and Local entries in a new array.
3. Format the dates in the new array to the desired format of "DD-MM-YYYY".
Here's a sample code in JavaScript to accomplish this:
Sql
// Sample data
var global = [
{ No: 1, Date: "1992-08-25 00:00:00.000" },
{ No: 2, Date: "2015-05-19 00:00:00.000" },
{ No: 3, Date: "2000-01-12 00:00:00.000" }
];
var local = [
{ No: 1, Date: "25.08.1992 00:00:00.000000000 GMT" },
{ No: 1, Date: "28.08.1992 00:00:00.000000000 GMT" },
{ No: 2, Date: "19.05.2015 00:00:00.000000000 GMT" },
{ No: 3, Date: "12.01.2000 00:00:00.000000000 GMT" },
{ No: 3, Date: "17.01.2000 00:00:00.000000000 GMT" }
];
// Result array
var result = [];
// Loop through each Global entry
for (var i = 0; i < global.length; i++) {
var globalDate = new Date(global[i].Date);
// Loop through each Local entry with the same No
for (var j = 0; j < local.length; j++) {
if (local[j].No === global[i].No) {
var localDate = new Date(local[j].Date);
// If the dates are not equal, store the entries in the result array
if (localDate.getTime() !== globalDate.getTime()) {
result.push({
Global: { No: global[i].No, Date: globalDate },
Local: { No: local[j].No, Date: localDate }
});
}
}
}
}
// Format the dates in the result array
for (var k = 0; k < result.length; k++) {
result[k].Global.Date =
result[k].Global.Date.getDate() +
"-" +
(result[k].Global.Date.getMonth() + 1) +
"-" +
result[k].Global.Date.getFullYear();
result[k].Local.Date =
result[k].Local.Date.getDate() +
"-" +
(result[k].Local.Date.getMonth() + 1) +
"-" +
result[k].Local.Date.getFullYear();
}
console.log(result);
This code will output the desired result:
yalm
[
{
Global: { No: 1, Date: "25-8-1992" },
Local: { No: 1, Date: "28-8-1992" }
},
{
Global: { No: 3, Date: "12-1
I hope this will help you :)
Thanks
James Millere
|
|
|
Powered by
FUDForum. Page generated in 0.02854 seconds
] 
Search
Help
Register
Login
Home
