The "date" is probably a timedate, and eventhough you see it as the only-the-date, the time makes it unique. Try to put also a groupby clause:
select distinct cast(DATE as date) from myschema.mytable GROUP BY DATE
untested.
Edit: somewhat tested on https://www.jdoodle.com/execute-sql-online/
create table foo (IDENTITY INT, LoginTime DATETIME);
INSERT INTO foo VALUES( 1, '2019-09-29 17:00:48.000' );
INSERT INTO foo VALUES( 2, '2019-09-29 17:29:02.000' );
INSERT INTO foo VALUES( 3, '2019-09-30 10:22:46.000' );
INSERT INTO foo VALUES( 4, '2019-09-30 11:16:23.000' );
--Select unique
SELECT distinct cast(LoginTime as date) FROM foo GROUP BY LoginTime;
returns: 2019 (just the year, strange)
EDIT 2: Ok, tried sqlite from Perl and SQLite does not have DATE... and returns the same "2019" as the online version. But it DOES have strftime and that made it work
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbfile = "sample.db";
my $dsn = "dbi:SQLite:dbname=$dbfile";
my $user = "x";
my $password = "x";
my $dbh = DBI->connect($dsn, $user, $password, {
PrintError => 0,
RaiseError => 1,
AutoCommit => 1,
FetchHashKeyName => 'NAME_lc',
});
if(-z $dbfile){
my $sql = <<'END_SQL';
CREATE TABLE foo (
id INTEGER PRIMARY KEY,
LoginTime DATE
)
END_SQL
$dbh->do($sql);
my @DATES = ('2019-09-29 17:00:48.000', '2019-09-29 17:29:02.000', '
+2019-09-30 10:22:46.000', '2019-09-30 11:16:23.000');
for(my $i=0;$i<=$#DATES;$i++){
$dbh->do('INSERT INTO foo (LoginTime) VALUES (?)',
undef,$DATES[$i]);
}
}
# This is what I thought would work
#my $sql = 'select distinct cast(LoginTime as date) from foo GROUP BY
+LoginTime';
# this actually works in SQLite (strftime)
my $sql = 'select distinct strftime("%d-%m-%Y",LoginTime) from foo';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array) {
print "[@row]\n";
}
$dbh->disconnect;
outputs:
[29-09-2019]
[30-09-2019]
Ok, after reading @Erix's comment, he's right. And Anonymous Monk (too bad for the points) pointed the error out, and it also occurs with SQLite. Data::Dumper shows the following after injecting 29th then 30th:
$VAR1 = [
[
'30-09-2019'
],
$VAR1->[0]
];
So all old values become a reference to the first entry of itself. So if you inject n fetchrow_arrayref() then you get n times the last pushed entry.
So to copy an arrayref: [@$ref] to get an AoA in @all_rows.
$sth->execute();
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
# Needs to be de-referenced (Anonymous monk's fix)
push(@all_rows, [@{$ref}]);
}
foreach my $row_ref (@all_rows) {
print "$row_ref->[0]\n";
}
And my first example worked because I used fetchrow_array() instead of fetchrow_arrayref() and went around the referencing problem (but references are faster in general, not by much, so keep using fetchrow_arrayref) | 