Professional Web Applications Themes

Getting rid of the time part - Microsoft SQL / MS SQL Server

select convert(varchar, getdate(), 112) -- -Vishal "Merwin" <pablombumdnj.edu> wrote in message news:0a9a01c340a7$8ce06c60$a501280aphx.gbl... > > How do you get rid of the time part in a datetime field > without doing a convert?? > > >...

  1. #1

    Default Re: Getting rid of the time part

    select convert(varchar, getdate(), 112)

    --
    -Vishal
    "Merwin" <pablombumdnj.edu> wrote in message
    news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    >
    > How do you get rid of the time part in a datetime field
    > without doing a convert??
    >
    >
    >

    Vishal Parkar Guest

  2. #2

    Default Re: Getting rid of the time part

    Use DATEPART function
    SELECT ltrim(rtrim(str(DATEPART(YY, getdate())))) + '-' +
    ltrim(rtrim(str(DATEPART(M, getdate())))) + '-' +
    ltrim(rtrim(str(DATEPART(D, getdate()))))

    Better use the CONVERT function.

    If you do not want the time part at all better declare the column as
    smalldatetime

    Thanks,
    Jagan Mohan
    Software Engineer


    "Merwin" <pablombumdnj.edu> wrote in message
    news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    >
    > How do you get rid of the time part in a datetime field
    > without doing a convert??
    >
    >
    >

    Jagan Mohan Guest

  3. #3

    Default Re: Getting rid of the time part

    Jagan Mohan wrote:
    > Use DATEPART function
    > SELECT ltrim(rtrim(str(DATEPART(YY, getdate())))) + '-' +
    > ltrim(rtrim(str(DATEPART(M, getdate())))) + '-' +
    > ltrim(rtrim(str(DATEPART(D, getdate()))))
    >
    > Better use the CONVERT function.
    >
    > If you do not want the time part at all better declare the column as
    > smalldatetime
    >
    Why? Check it out in BOL: smalldatetime stores both date and time as well as
    datetime.


    Bob Barrows Guest

  4. #4

    Default Re: Getting rid of the time part

    SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Merwin" <pablombumdnj.edu> wrote in message
    news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    >
    > How do you get rid of the time part in a datetime field
    > without doing a convert??
    >
    >
    >

    Jacco Schalkwijk Guest

  5. #5

    Default Re: Getting rid of the time part

    Jacco,

    Casting to an int will result in "rounding up" if the time is after noon.
    The FLOOR() function is a better choice if one is attempting to do numeric
    conversion. e.g.:

    cast(floor(cast(getdate() as float)) as datetime)

    Nicole


    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:ufamn$KQDHA.1556TK2MSFTNGP10.phx.gbl...
    > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Merwin" <pablombumdnj.edu> wrote in message
    > news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    > >
    > > How do you get rid of the time part in a datetime field
    > > without doing a convert??
    > >
    > >
    > >
    >
    >

    Nicole Calinoiu Guest

  6. #6

    Default Re: Getting rid of the time part

    Ah, I forgot about the rounding up :$

    I know the way to do it wiht floor and I though this was simpler, but
    alas...


    "Nicole Calinoiu" <nicolecsomewhere.net> wrote in message
    news:%23wVQ67LQDHA.1624tk2msftngp13.phx.gbl...
    > Jacco,
    >
    > Casting to an int will result in "rounding up" if the time is after noon.
    > The FLOOR() function is a better choice if one is attempting to do numeric
    > conversion. e.g.:
    >
    > cast(floor(cast(getdate() as float)) as datetime)
    >
    > Nicole
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:ufamn$KQDHA.1556TK2MSFTNGP10.phx.gbl...
    > > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Merwin" <pablombumdnj.edu> wrote in message
    > > news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    > > >
    > > > How do you get rid of the time part in a datetime field
    > > > without doing a convert??
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  7. #7

    Default Re: Getting rid of the time part

    Sorry, didn't see the Floor suggestion... my eyes must be going out on me.
    It's also a lot better then 'Dateadd', so I guess my intelligence (or at least the little I had) is going out on me too.
    Ahh, the pleasures of getting old.

    <raydan> a écrit dans le message de news: [email]eTUfoIMQDHA.2300TK2MSFTNGP11.phx.gbl[/email]...
    Good catch Nicole.
    SELECT CAST(CAST(dateadd(hh,-12, GETDATE()) AS int) AS DATETIME)
    should cover this.

    "Nicole Calinoiu" <nicolecsomewhere.net> a écrit dans le message de news: #wVQ67LQDHA.1624tk2msftngp13.phx.gbl...
    > Jacco,
    >
    > Casting to an int will result in "rounding up" if the time is after noon.
    > The FLOOR() function is a better choice if one is attempting to do numeric
    > conversion. e.g.:
    >
    > cast(floor(cast(getdate() as float)) as datetime)
    >
    > Nicole
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:ufamn$KQDHA.1556TK2MSFTNGP10.phx.gbl...
    > > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Merwin" <pablombumdnj.edu> wrote in message
    > > news:0a9a01c340a7$8ce06c60$a501280aphx.gbl...
    > > >
    > > > How do you get rid of the time part in a datetime field
    > > > without doing a convert??
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Guest

Similar Threads

  1. Replies: 3
    Last Post: January 21st, 12:16 AM
  2. CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
    By bayfield in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 8th, 04:34 AM
  3. Good Freelance/Part-Time Lingo developer wanted
    By SpecialDude in forum Macromedia Director 3D
    Replies: 0
    Last Post: August 10th, 04:52 PM
  4. Formatting a time field to 24 hour time (Military time) in the Datagrid
    By David Hearn in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: September 10th, 02:50 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