Post reply

Converting smalldatetime to lose the minutes

  • July 13, 2005 at 3:53 am

    #66681

    I need hourly information from a field which is storing data by the minute.

    So I have Pie sold at '12/06/05 11.23AM' smalldatetime.

    I need to convert to 12/06/05 11.00AM

    So that I can can all products sold in the hours starting at 11AM

    I tried using varchar which is great at restricting it down to 12/06/04 11, but I need the 11.00AM.

    I feel I want to do a cross between a replace and a trim....?

    Can you help?

    Thank you

    Sharon

     

     

  • July 13, 2005 at 4:40 am

    #573815

     

    Try this one.

    Select convert(varchar(10),'12/06/05 11.23AM' ,121)

     

    Regards,

    Ramesh K

  • July 13, 2005 at 4:46 am

    #573816

    Thank you, but that loses my 11.00AM, and I still need the hour to get hourly information

  • July 13, 2005 at 5:39 am

    #573821

    If you are storing the SOLD as DATETIME the below may help

    DECLARE @VAR DATETIME

    SET     @VAR = '2005-07-13 05:42:08.070'

    SELECT CONVERT(CHAR(10), @VAR, 101) + SPACE(1) + LEFT(CONVERT(CHAR(8), @VAR, 108), 2) + ':00' + SPACE(1)

      + RIGHT(CAST(CONVERT(VARCHAR(26), @VAR, 109) AS VARCHAR(26)), 2)

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 13, 2005 at 6:19 am

    #573833

    Is this only for a report? Cause you don't need to change the data in the base table (not sure you're not doing that ).

    There are more efficient ways to calculate this sort of info. Can you pass the table definition some sample data and the select you need so I can pass it on?

  • July 13, 2005 at 8:29 am

    #573928

    Can you use two pieces of data instead?

     

    Use select convert(varchar, @yourDateVariable, 1) to get the date

    Use select datepart(hour,(@yourDateVariable)) to get the hour.

     

    If you can only compare to one field, use this to get the date and the hour, no minutes. This will do it:

    select dateadd(hour, datepart(hour, YourDateField), convert(varchar, YourDateField, 1))

  • July 13, 2005 at 10:16 am

    #574000

    Thanks that's brill, wish I'd thought if it!!!!

  • July 13, 2005 at 9:29 pm

    #574231

    Trying my luck....

     

    declare @date datetime

    select @date = getdate()

    select convert(varchar(22),@date,100)

  • July 14, 2005 at 2:10 am

    #574277

    I can't try this but modifying the best way to get a date only with no time

    SELECT DATEADD(day, DATEDIFF(day, 0, <basedatetime&gt, 0)

    Try this:

    SELECT DATEADD(hour, DATEDIFF(hour, 0, <basedatetime&gt, 0)

    This keeps it the numeric domain with no converts

    An old post in this forum tested various ways of stripping times and over many rows, this way was quicker than anything with cast or convert in it

     

    It may work

  • July 14, 2005 at 2:52 pm

    #574591

    Select Stuff(Convert(VarChar,GetDate(),120),15,5,'00:00')


    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • July 14, 2005 at 3:06 pm

    #574600

    Or...

    Select Cast(Floor(Cast(GetDate() as float)*24.0)/24.0 as SmallDateTime)


    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • July 15, 2005 at 12:00 am

    #574673

    Another one that keeps it numeric..

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply