By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

SELECT DISTINCT Problem

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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


Jul 20 '05 #3

P: n/a
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

Jul 20 '05 #4

P: n/a
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
Jul 20 '05 #5

P: n/a
Thanks very much Helmut! It works perfectly!

"hwoess" <in****@iis-edv.at> wrote in message news:<40***********************@newsreader01.highw ay.telekom.at>...
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.