SELECT DISTINCT Problem

[Martin Feuersteiner]

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDate), CONVERT(varchar(10),TransactionDate,104)
AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

[Martin Feuersteiner]

Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransactionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:c5**********@titan.btinternet.com...

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDate), CONVERT(varchar(10),TransactionDate,104) AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

[Martin Feuersteiner]

Ok, if I just use
SELECT DISTINCT CONVERT(varchar(10),TransactionDate,104) FROM
i2b_keytransactionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?

"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:c5**********@sparta.btinternet.com...

Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransactionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:c5**********@titan.btinternet.com...

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and once as varchar as I'm populating a drop-down with Text/Value pairs. So I can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDate),

CONVERT(varchar(10),TransactionDate,104)

AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

[Martin Feuersteiner]

Ok, if I just use
SELECT DISTINCT CONVERT(varchar(10),TransactionDate,104) FROM
i2b_keytransactionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?
"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:c5**********@titan.btinternet.com...

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDate), CONVERT(varchar(10),TransactionDate,104) AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

[hwoess]

try this:

SELECT DISTINCT convert(datetime,cast(TransactionDate as int)),
CONVERT(varchar(10),TransactionDate,104)
AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

bye,
Helmut

[Martin]

Thanks very much Helmut! It works perfectly!

"hwoess" <in****@iis-edv.at> wrote in message news:<40***********************@newsreader01.highw ay.telekom.at>...