Professional Web Applications Themes

remove time from getdate() - Microsoft SQL / MS SQL Server

(select convert(varchar,c1,112) from #temp) change above line -- --Vishal "chris" <chrisfin2000.com> wrote in message news:03a901c344b3$9d6fcb10$a101280aphx.gbl... > sql2k sp2 > > Im trying to figure out how to return data from a datetime > column that has had getdate() inserted into it without the > time. > > create table #temp > (c1 datetime) > insert into #temp (c1) > values (getdate()) > (select convert(datetime,c1,110) from #temp) > drop table #temp > > (desired output = date but not time) > > Ive tried all the different styles and not only do they > not remove the time but they dont ...

  1. #1

    Default Re: remove time from getdate()

    (select convert(varchar,c1,112) from #temp)
    change above line

    --
    --Vishal

    "chris" <chrisfin2000.com> wrote in message
    news:03a901c344b3$9d6fcb10$a101280aphx.gbl...
    > sql2k sp2
    >
    > Im trying to figure out how to return data from a datetime
    > column that has had getdate() inserted into it without the
    > time.
    >
    > create table #temp
    > (c1 datetime)
    > insert into #temp (c1)
    > values (getdate())
    > (select convert(datetime,c1,110) from #temp)
    > drop table #temp
    >
    > (desired output = date but not time)
    >
    > Ive tried all the different styles and not only do they
    > not remove the time but they dont even output the way BOL
    > describes.
    >
    > Any thoughts???
    > Thanks.

    Vishal Parkar Guest

  2. #2

    Default Re: remove time from getdate()

    Correction:
    values (select convert(varchar,getdate(),112))
    --change the above statement.

    --
    --Vishal

    "chris" <chrisfin2000.com> wrote in message
    news:03a901c344b3$9d6fcb10$a101280aphx.gbl...
    > sql2k sp2
    >
    > Im trying to figure out how to return data from a datetime
    > column that has had getdate() inserted into it without the
    > time.
    >
    > create table #temp
    > (c1 datetime)
    > insert into #temp (c1)
    > values (getdate())
    > (select convert(datetime,c1,110) from #temp)
    > drop table #temp
    >
    > (desired output = date but not time)
    >
    > Ive tried all the different styles and not only do they
    > not remove the time but they dont even output the way BOL
    > describes.
    >
    > Any thoughts???
    > Thanks.

    Vishal Parkar Guest

  3. #3

    Default Re: remove time from getdate()

    > Im trying to figure out how to return data from a datetime
    > column that has had getdate() inserted into it without the
    > time.
    >
    > create table #temp
    > (c1 datetime)
    > insert into #temp (c1)
    > values (getdate())
    > (select convert(datetime,c1,110) from #temp)
    > drop table #temp
    >
    > (desired output = date but not time)
    The convert should be to char or varchar, not to datetime. Since the
    datetime datatype includes both date and time information, and conversion
    *to* datetime will include both date and time information.

    Why not insert the date without the time in the first place, if the time is
    irrelevant?

    INSERT #temp(c1) VALUES({fn CURDATE()})

    INSERT #temp(c1) VALUES(GETDATE()-{fn CURRENT_TIME()})

    INSERT #temp(c1) VALUES(CONVERT(CHAR(8), GETDATE(), 112))

    Then,

    SELECT c1 FROM #temp

    Or, you can just say (depending on how you want "date but not time" to
    look):

    SELECT CONVERT(CHAR(8), c1, 112) FROM #temp

    SELECT CONVERT(CHAR(10), c1, 101) FROM #temp

    SELECT CONVERT(CHAR(10), c1, 103) FROM #temp

    SELECT CONVERT(DATETIME, CONVERT(CHAR(8), c1, 112)) FROM #temp
    > Ive tried all the different styles and not only do they
    > not remove the time but they dont even output the way BOL
    > describes.
    Could you show an example? I have no problems using the datetime formats
    and styles outlined in books online... so I'm guessing you didn't really
    use an example directly from books online, but did a casual query
    construction from memory...

    For more information, see the following articles:

    [url]http://www.aspfaq.com/2206[/url]
    [url]http://www.aspfaq.com/2460[/url]
    [url]http://www.aspfaq.com/2464[/url]


    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: remove time from getdate()

    Hi

    You need to convert from datatime to a character type not datetime.

    SELECT convert(char(10),c1,110) FROM #temp

    John

    "chris" <chrisfin2000.com> wrote in message
    news:03a901c344b3$9d6fcb10$a101280aphx.gbl...
    > sql2k sp2
    >
    > Im trying to figure out how to return data from a datetime
    > column that has had getdate() inserted into it without the
    > time.
    >
    > create table #temp
    > (c1 datetime)
    > insert into #temp (c1)
    > values (getdate())
    > (select convert(datetime,c1,110) from #temp)
    > drop table #temp
    >
    > (desired output = date but not time)
    >
    > Ive tried all the different styles and not only do they
    > not remove the time but they dont even output the way BOL
    > describes.
    >
    > Any thoughts???
    > Thanks.

    John Bell Guest

  5. #5

    Default Re: remove time from getdate()

    Could you show an example? I have no problems using the
    datetime formats
    and styles outlined in books online...

    All of you have answered my question already but FYI what
    I was referring to is when you write the query the way I
    wrote it:

    create table #temp
    (c1 datetime)
    insert into #temp (c1)
    values (getdate())
    (select convert(datetime,c1,102) from #temp)
    drop table #temp

    it doesnt matter if the style is 101,102,106 etc. the
    results are still the same. Obviously it will be different
    now that I will be converting to char instead. Thanks.

    >-----Original Message-----
    >> Im trying to figure out how to return data from a
    datetime
    >> column that has had getdate() inserted into it without
    the
    >> time.
    >>
    >> create table #temp
    >> (c1 datetime)
    >> insert into #temp (c1)
    >> values (getdate())
    >> (select convert(datetime,c1,110) from #temp)
    >> drop table #temp
    >>
    >> (desired output = date but not time)
    >
    >The convert should be to char or varchar, not to
    datetime. Since the
    >datetime datatype includes both date and time
    information, and conversion
    >*to* datetime will include both date and time information.
    >
    >Why not insert the date without the time in the first
    place, if the time is
    >irrelevant?
    >
    >INSERT #temp(c1) VALUES({fn CURDATE()})
    >
    >INSERT #temp(c1) VALUES(GETDATE()-{fn CURRENT_TIME()})
    >
    >INSERT #temp(c1) VALUES(CONVERT(CHAR(8), GETDATE(), 112))
    >
    >Then,
    >
    >SELECT c1 FROM #temp
    >
    >Or, you can just say (depending on how you want "date but
    not time" to
    >look):
    >
    >SELECT CONVERT(CHAR(8), c1, 112) FROM #temp
    >
    >SELECT CONVERT(CHAR(10), c1, 101) FROM #temp
    >
    >SELECT CONVERT(CHAR(10), c1, 103) FROM #temp
    >
    >SELECT CONVERT(DATETIME, CONVERT(CHAR(8), c1, 112)) FROM
    #temp
    >
    >> Ive tried all the different styles and not only do they
    >> not remove the time but they dont even output the way
    BOL
    >> describes.
    >
    >Could you show an example? I have no problems using the
    datetime formats
    >and styles outlined in books online... so I'm guessing
    you didn't really
    >use an example directly from books online, but did a
    casual query
    >construction from memory...
    >
    >For more information, see the following articles:
    >
    >[url]http://www.aspfaq.com/2206[/url]
    >[url]http://www.aspfaq.com/2460[/url]
    >[url]http://www.aspfaq.com/2464[/url]
    >
    >
    >.
    >
    chris Guest

  6. #6

    Default Re: remove time from getdate()

    Please re-read Aaron's reply. The examples you posted, again shows CONVERT
    done to datetime datatype. CONVERT functions on datetime should be done to
    char/varchar datatypes to yield the required results.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  7. #7

    Default Re: remove time from getdate()

    >Yes, and I thought we explained that to you: you need to
    convert to
    >CHAR/VARCHAR, not to DATETIME.
    You did. Which is why I followed it up my last reply with:

    ***** Obviously it will be different now that I will be
    converting to char instead.*****

    I was simply explaining why it WAS working the way it WAS
    working. Not that I will do the same thing now and expect
    a different result.

    Thanks again for the help.
    Chris
    >-----Original Message-----
    >> All of you have answered my question already but FYI
    what
    >> I was referring to is when you write the query the way I
    >> wrote it:
    >>
    >> create table #temp
    >> (c1 datetime)
    >> insert into #temp (c1)
    >> values (getdate())
    >> (select convert(datetime,c1,102) from #temp)
    >> drop table #temp
    >>
    >> it doesnt matter if the style is 101,102,106 etc. the
    >> results are still the same.
    >
    >Yes, and I thought we explained that to you: you need to
    convert to
    >CHAR/VARCHAR, not to DATETIME. I don't believe any of
    the examples in Books
    >Online, that demonstrate how to extract portions of a
    DATETIME in specific
    >formats, use a CONVERT *to* DATETIME...
    >
    >
    >.
    >
    chris Guest

  8. #8

    Default Re: remove time from getdate()

    Maybe you should look at SET DATEFORMAT?

    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/url]
    set-set_052s.asp?frame=true

    John

    "chris" <chrisfin2000.com> wrote in message
    news:01dc01c344bb$d170ce70$a501280aphx.gbl...
    > >Yes, and I thought we explained that to you: you need to
    > convert to
    > >CHAR/VARCHAR, not to DATETIME.
    >
    > You did. Which is why I followed it up my last reply with:
    >
    > ***** Obviously it will be different now that I will be
    > converting to char instead.*****
    >
    > I was simply explaining why it WAS working the way it WAS
    > working. Not that I will do the same thing now and expect
    > a different result.
    >
    > Thanks again for the help.
    > Chris
    >
    > >-----Original Message-----
    > >> All of you have answered my question already but FYI
    > what
    > >> I was referring to is when you write the query the way I
    > >> wrote it:
    > >>
    > >> create table #temp
    > >> (c1 datetime)
    > >> insert into #temp (c1)
    > >> values (getdate())
    > >> (select convert(datetime,c1,102) from #temp)
    > >> drop table #temp
    > >>
    > >> it doesnt matter if the style is 101,102,106 etc. the
    > >> results are still the same.
    > >
    > >Yes, and I thought we explained that to you: you need to
    > convert to
    > >CHAR/VARCHAR, not to DATETIME. I don't believe any of
    > the examples in Books
    > >Online, that demonstrate how to extract portions of a
    > DATETIME in specific
    > >formats, use a CONVERT *to* DATETIME...
    > >
    > >
    > >.
    > >

    John Bell Guest

Similar Threads

  1. Date and Time stamp on photo: how to remove
    By fred vincent in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 4
    Last Post: February 15th, 10:51 PM
  2. #25787 [Bgs]: 'yday' in getdate() function is out by one day
    By sniper@php.net in forum PHP Development
    Replies: 1
    Last Post: October 8th, 10:46 AM
  3. #25787 [Opn->Bgs]: 'yday' in getdate() function is out by one day
    By sniper@php.net in forum PHP Development
    Replies: 2
    Last Post: October 8th, 10:40 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