Professional Web Applications Themes

Display datetime value as date only - Microsoft SQL / MS SQL Server

How do I convert a value that contains date and time values to the date alone? In Access, I'd just use the DateValue() function. -- HTH Dale Fye...

  1. #1

    Default Display datetime value as date only

    How do I convert a value that contains date and time values to the
    date alone?

    In Access, I'd just use the DateValue() function.

    --
    HTH

    Dale Fye



    Dale Fye Guest

  2. #2

    Default Re: Display datetime value as date only

    try similar to the following

    Select convert(varchar(12), getdate(), 101) --Also look into other Style
    (third argument) options in BOL

    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:OeNLc78RDHA.212TK2MSFTNGP10.phx.gbl...
    > How do I convert a value that contains date and time values to the
    > date alone?
    >
    > In Access, I'd just use the DateValue() function.
    >
    > --
    > HTH
    >
    > Dale Fye
    >
    >
    >

    Prasad Koukuntla Guest

  3. #3

    Default Re: Display datetime value as date only

    SELECT CONVERT(CHAR(8), DateColumn, 112) FROM table_name




    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:OeNLc78RDHA.212TK2MSFTNGP10.phx.gbl...
    > How do I convert a value that contains date and time values to the
    > date alone?
    >
    > In Access, I'd just use the DateValue() function.
    >
    > --
    > HTH
    >
    > Dale Fye
    >
    >
    >

    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Display datetime value as date only

    Aaron,

    Doesn't this return a string? What I really need to do is determine
    whether a parameter is between 00:00:00 AM on the [StartDate] and
    24:00:00 on the [EndDate].

    I am stuck with the format of these two fields. But I really need
    '6/12/2003 00:00:00' instead of '6/12/2003 14:00:00' as the comparison
    against the [StartDate] and '6/14/2003 00:00:00' instead of '6/13/2003
    19:00:00' as the comparison against the [EndDate].

    My where clause in ACCESS would look something like:

    WHERE dtParameter BETWEEN DateValue([StartDate]) AND DATEADD("d", 1,
    DateValue([EndDate]))

    --
    HTH

    Dale Fye


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:O9q$U#8RDHA.304tk2msftngp13.phx.gbl...
    SELECT CONVERT(CHAR(8), DateColumn, 112) FROM table_name




    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:OeNLc78RDHA.212TK2MSFTNGP10.phx.gbl...
    > How do I convert a value that contains date and time values to the
    > date alone?
    >
    > In Access, I'd just use the DateValue() function.
    >
    > --
    > HTH
    >
    > Dale Fye
    >
    >
    >


    Dale Fye Guest

  5. #5

    Default Re: Display datetime value as date only

    > Doesn't this return a string?

    Yes, you said you wanted date without time. If you are changing your
    requirement to being "I want datetime value with the date but the time at
    midnight" then:

    SELECT CONVERT(DATETIME, CONVERT(CHAR(8), DateColumn, 112)) FROM table_name
    > My where clause in ACCESS would look something like:
    >
    > WHERE dtParameter BETWEEN DateValue([StartDate]) AND DATEADD("d", 1,
    > DateValue([EndDate]))
    How about

    WHERE StartDate <= dtParameter AND EndDate >= dtParameter

    or

    WHERE dtParameter >= StartDate AND dtParameter < (EndDate+1)

    ?


    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Display datetime value as date only

    Aaron,

    Thanks for the syntax on the CONVERT. I think I will just have to
    write my own DateValue function that uses this syntax to strip the
    time portion off of a datetime value.

    The other suggestions won't work. The translate as follows for a
    simple example.

    WHERE StartDate <= dtParameter AND EndDate >= dtParameter

    translates as:
    WHERE '6/12/2003 14:00:00' <= '6/12/2003 09:00:00'
    AND '6/13/2003 19:00:00' >= '6/12/2003 09:00:00'

    which returns FALSE. Instead of

    WHERE '6/12/2003 00:00:00' <= '6/12/2003 09:00:00'
    AND '6/14/2003 00:00:00' >= '6/12/2003 09:00:00'

    which returns TRUE


    --
    HTH

    Dale Fye


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:e80CJZ9RDHA.2332TK2MSFTNGP10.phx.gbl...
    > Doesn't this return a string?
    Yes, you said you wanted date without time. If you are changing your
    requirement to being "I want datetime value with the date but the time
    at
    midnight" then:

    SELECT CONVERT(DATETIME, CONVERT(CHAR(8), DateColumn, 112)) FROM
    table_name
    > My where clause in ACCESS would look something like:
    >
    > WHERE dtParameter BETWEEN DateValue([StartDate]) AND DATEADD("d", 1,
    > DateValue([EndDate]))
    How about

    WHERE StartDate <= dtParameter AND EndDate >= dtParameter

    or

    WHERE dtParameter >= StartDate AND dtParameter < (EndDate+1)

    ?



    Dale Fye Guest

  7. #7

    Default Re: Display datetime value as date only

    > WHERE StartDate <= dtParameter AND EndDate >= dtParameter
    >
    > translates as:
    > WHERE '6/12/2003 14:00:00' <= '6/12/2003 09:00:00'
    > AND '6/13/2003 19:00:00' >= '6/12/2003 09:00:00'
    >
    > which returns FALSE. Instead of
    >
    > WHERE '6/12/2003 00:00:00' <= '6/12/2003 09:00:00'
    > AND '6/14/2003 00:00:00' >= '6/12/2003 09:00:00'
    >
    > which returns TRUE
    Yes, and I was assuming you could do this, before you run your query:

    SET dtParameter = CONVERT(DATETIME, CONVERT(CHAR(8), dtParameter, 112))

    Or, if the time is meaningless, why pass it into the stored procedure at
    all? Just pass '20030612'

    (Please don't use formats like 6/12/2003... is that June 12 or December 6th?
    Depends on who you ask, or what regional settings / Dateformat settings your
    server is in, and opens up far too much ambiguity to rely on.)

    Don't write your own function to strip times off dates. The functions I've
    given you will already do that.


    Aaron Bertrand - MVP Guest

Similar Threads

  1. XML/XSD types xs:date, xs:dateTime, (and xs:duration)
    By AndyArmitage in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: January 15th, 10:23 AM
  2. Show Date only from DateTime in DataGrid
    By Xarky in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: May 24th, 01:06 AM
  3. Separated date and time or one datetime field
    By Krzysztof Piotrowski in forum PHP Development
    Replies: 0
    Last Post: April 24th, 07:12 PM
  4. get week from date/datetime field
    By Pohoda in forum Informix
    Replies: 3
    Last Post: October 2nd, 02:19 PM
  5. Compares just date instead datetime
    By Wayne Gillespie in forum Microsoft Access
    Replies: 2
    Last Post: July 22nd, 02:52 PM

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