Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Minnesota
    Posts
    3

    Smile Unanswered: Newbie Needs Help Getting Date

    Hello,

    I have a field in a table that automatically saves the Date and Time.
    My field looks like this: 10/10/2003 2:24:40 PM

    I need to retrieve ONLY the date portion of this field. Is there a simple way to do this? Any help is greatly appreciated!
    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Newbie Needs Help Getting Date

    SELECT CONVERT(varchar(26),GetDate(),1)


    Look up CONVERT in BOL for more options...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but you'll need to cast is as a date value again if you want to use it like a date:

    SELECT CAST(CONVERT(varchar(26),GetDate(),1) as DateTime)


    ...or my preference because it sorts correctly as varchar...
    SELECT CAST(CONVERT(varchar(10),GetDate(),120) as DateTime)


    Everybody has their own preference for this, but the general method of casting as a string and then back to datetime (if necessary) is standard.

    blindman

    P.S.: If you still need help getting a date, check out this link:
    http://personals.yahoo.com/
    Last edited by blindman; 10-22-03 at 11:31.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    heh, nice P.S. -- this forum needs the occasional chuckle

    by the way, style 120, which blindman mentioned, is the ISO standard format yyyy-mm-dd

    is sorts nicely as a varchar because it goes from highest to lowest (well, i didn't say that right, but i hope you know what it means)

    in addition, ISO format is always correctly interpreted by all databases when inserting dates

    i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    39
    Originally posted by r937
    i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)
    Funny, I'm trying to explain that to my fellow programmers right now ... They've got the most intricate functions to fix those problems and they all screw up when someone changes the regional settings on their servers

  6. #6
    Join Date
    Oct 2003
    Location
    Minnesota
    Posts
    3

    Re: Newbie Needs Help Getting Date

    Nice PS: However, my wife MAY be mad at me for looking at personals, or maybe she is tired of me and would like that!!! LOL

    I may not have described my problem well enough or probably I am not understanding since I am new to all this. This is more of what I need...

    I have these dates and times: Field Name is GetDate

    10/10/2003 2:24:40 PM
    10/10/2003 3:34:41 PM
    10/20/2003 2:24:40 PM
    10/20/2003 6:54:20 PM
    10/21/2003 2:24:40 PM
    10/21/2003 8:34:43 PM

    I need to pull out the different dates and display only one date:

    10/10/2003
    10/20/2003
    10/21/2003

    Does that make sense????
    Thanks again!!!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please tell me you didn't name your field "GetDate".

    You actually are performing two operations, the first is to truncate datetime values (which we've shown you how to do) and the second is reducing the result set to a single row for each date. You can do this using either the SELECT DISTINCT syntax, or if you need to perform aggregate functions on other columns (sum, count, avg etc...) you can use the GROUP BY syntax.

    Read up on the SELECT statement in Books Online. It is very powerful, has many options, and is 90%+ of the statements DBA's write.

    blindman

  8. #8
    Join Date
    Oct 2003
    Location
    Minnesota
    Posts
    3

    Re: Newbie Needs Help Getting Date

    Thanks for the assistance...

    I didn't name the field getDate: I misstyped... it is DateSubmitted...

    Thinking dateSubmitted and getDate came out of fingers... hehehe

    Thanks so much for everything!!!!

Posting Permissions

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