Professional Web Applications Themes

datetime problem for sql gurus - Microsoft SQL / MS SQL Server

I have a table of structure (message_id, event_id, date_time, note) where date_time is basically a datetime field. I need to determine, using the datetime field, the total number of unique 'dates' ... eg. the dates below should produce 3 because there are 3 unique 'dates' in them: 8/3/2003 10:50:00 8/3/2003 10:55:00 8/4/2003 12:50:00 8/5/2003 19:59:00 8/5/2003 15:30:00 Please let me know if you have any ideas! Thanks...

  1. #1

    Default datetime problem for sql gurus

    I have a table of structure (message_id, event_id,
    date_time, note) where date_time is basically a datetime
    field.

    I need to determine, using the datetime field, the
    total number of unique 'dates' ... eg. the dates below
    should produce 3 because there are 3 unique 'dates' in
    them:

    8/3/2003 10:50:00
    8/3/2003 10:55:00
    8/4/2003 12:50:00
    8/5/2003 19:59:00
    8/5/2003 15:30:00

    Please let me know if you have any ideas! Thanks
    Shumon Guest

  2. #2

    Default Re: datetime problem for sql gurus

    "Shumon S" <com> wrote in
    news:9e2b01c35c34$78067610$gbl:
     

    select count(distinct date_time) from table_name seems to do the
    trick...

    Cheers,
    --
    Use Reply-To address or replace deadspam with hotmail
    Koan Guest

  3. #3

    Default Re: datetime problem for sql gurus

    something like this....

    create table #example (date_time datetime)
    insert into #example values ('8/3/2003 10:50:00')
    insert into #example values ('8/3/2003 10:55:00')
    insert into #example values ('8/4/2003 12:50:00')
    insert into #example values ('8/5/2003 19:59:00')
    insert into #example values ('8/5/2003 15:30:00')


    SELECT DISTINCT CONVERT(varchar(10),date_time,101) AS TheColumn FROM #example


    --
    Keith, SQL Server MVP

    "Shumon S" <com> wrote in message news:9e2b01c35c34$78067610$gbl... 
    Keith Guest

  4. #4

    Default datetime problem for sql gurus

    SELECT Convert(char(10),date_time,101) AS UniqueDate
    , Count(*) AS MsgCount
    FROM YourTable
    GROUP BY Convert(char(10),date_time,101)

    Good luck!
    --Angel
    -------------------------------
    Every time I lose my mind, I wonder
    if it's really worth finding.
     
    AngelWPB Guest

  5. #5

    Default Re: datetime problem for sql gurus

    Use convert function:
    select distinct convert(varchar(15), dt_column, 101) from table

    --
    -Vishal

    "Shumon S" <com> wrote in message
    news:9e2b01c35c34$78067610$gbl... 


    Vishal Guest

  6. #6

    Default Re: datetime problem for sql gurus

    Koan G <com> wrote in
    news:46.248.16:
     
    >
    > select count(distinct date_time) from table_name seems to do the
    > trick...[/ref]

    .... having said which, select count(distinct convert(varchar,
    date_time, 101)) from table_name has the distinct advantage of
    ignoring the times! :-)

    Sorry for the brain .

    Cheers,
    --
    Use Reply-To address or replace deadspam with hotmail
    Koan Guest

  7. #7

    Default Re: datetime problem for sql gurus

    Thanks very much folks - all your suggestions were great, and worked
    very well ...


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Shumon Guest

Similar Threads

  1. DateTime Problem with .NET WebService
    By CI2 in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: March 24th, 11:46 AM
  2. DateTime problem
    By FeyBaby in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 2nd, 07:29 AM
  3. dateTime = Null problem
    By Jake in forum ASP.NET General
    Replies: 1
    Last Post: July 18th, 04:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139