Strip Time from Datetime

Discussion in 'ASP General' started by scott, Jul 12, 2005.

  1. scott

    scott Guest

    I have a field with datetime values like below LISTING 1. Can someone help
    me write code strip the time part so only values like "7/15/2005" will be
    left.

    Note - We must be able to strip dates with double digit months and days, so
    i can't just use the right function with a hard coded parameter.


    LISTING 1

    mydatetimevalue = "7/15/2005 8:00:00 AM"
    scott, Jul 12, 2005
    #1
    1. Advertising

  2. scott

    Steven Burn Guest

    0 = vbGeneralDate
    1 = vbLongDate
    2 = vbShortDate
    3 = vbLongTime
    4 = vbShortTime

    Response.Write FormatDateTime(mydatetimevalue, 2)

    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    "scott" <> wrote in message
    news:...
    > I have a field with datetime values like below LISTING 1. Can someone help
    > me write code strip the time part so only values like "7/15/2005" will be
    > left.
    >
    > Note - We must be able to strip dates with double digit months and days,

    so
    > i can't just use the right function with a hard coded parameter.
    >
    >
    > LISTING 1
    >
    > mydatetimevalue = "7/15/2005 8:00:00 AM"
    >
    >
    Steven Burn, Jul 12, 2005
    #2
    1. Advertising

  3. scott

    scott Guest

    i've got to actually strip the time off, not just display because the date
    is a parameter for a sql sproc.


    "Steven Burn" <> wrote in message
    news:%23O8sR%...
    >0 = vbGeneralDate
    > 1 = vbLongDate
    > 2 = vbShortDate
    > 3 = vbLongTime
    > 4 = vbShortTime
    >
    > Response.Write FormatDateTime(mydatetimevalue, 2)
    >
    > Regards
    >
    > Steven Burn
    > Ur I.T. Mate Group
    > www.it-mate.co.uk
    >
    > Keeping it FREE!
    >
    > "scott" <> wrote in message
    > news:...
    >> I have a field with datetime values like below LISTING 1. Can someone
    >> help
    >> me write code strip the time part so only values like "7/15/2005" will be
    >> left.
    >>
    >> Note - We must be able to strip dates with double digit months and days,

    > so
    >> i can't just use the right function with a hard coded parameter.
    >>
    >>
    >> LISTING 1
    >>
    >> mydatetimevalue = "7/15/2005 8:00:00 AM"
    >>
    >>

    >
    >
    scott, Jul 12, 2005
    #3
  4. scott wrote:
    > I have a field with datetime values like below LISTING 1. Can someone
    > help me write code strip the time part so only values like
    > "7/15/2005" will be left.
    >
    > Note - We must be able to strip dates with double digit months and
    > days, so i can't just use the right function with a hard coded
    > parameter.
    >
    > LISTING 1
    >
    > mydatetimevalue = "7/15/2005 8:00:00 AM"


    Use the FormatDateTime function. See:
    http://www.aspfaq.com/show.asp?id=2313

    Or are you are asking how to do it in a query? If so, you need to tell us
    what database you are using.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jul 12, 2005
    #4
  5. scott

    scott Guest

    I need just need some nifty use of string functions to chop off the time and
    leave m/d/yyyy string. it's going to a sproc as a parameter so time needs to
    be choped off, not just displayed right.


    "Bob Barrows [MVP]" <> wrote in message
    news:O1QU$...
    > scott wrote:
    >> I have a field with datetime values like below LISTING 1. Can someone
    >> help me write code strip the time part so only values like
    >> "7/15/2005" will be left.
    >>
    >> Note - We must be able to strip dates with double digit months and
    >> days, so i can't just use the right function with a hard coded
    >> parameter.
    >>
    >> LISTING 1
    >>
    >> mydatetimevalue = "7/15/2005 8:00:00 AM"

    >
    > Use the FormatDateTime function. See:
    > http://www.aspfaq.com/show.asp?id=2313
    >
    > Or are you are asking how to do it in a query? If so, you need to tell us
    > what database you are using.
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    scott, Jul 12, 2005
    #5
  6. scott

    McKirahan Guest

    "scott" <> wrote in message
    news:...
    > I have a field with datetime values like below LISTING 1. Can someone help
    > me write code strip the time part so only values like "7/15/2005" will be
    > left.
    >
    > Note - We must be able to strip dates with double digit months and days,

    so
    > i can't just use the right function with a hard coded parameter.
    >
    >
    > LISTING 1
    >
    > mydatetimevalue = "7/15/2005 8:00:00 AM"



    WScript.Echo Split(mydatetimevalue," ")(0)
    McKirahan, Jul 12, 2005
    #6
  7. Then you'll want to read this:
    http://www.aspfaq.com/show.asp?id=2040

    What datatype is the parameter?

    Bob Barrows

    scott wrote:
    > I need just need some nifty use of string functions to chop off the
    > time and leave m/d/yyyy string. it's going to a sproc as a parameter
    > so time needs to be choped off, not just displayed right.
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:O1QU$...
    >> scott wrote:
    >>> I have a field with datetime values like below LISTING 1. Can
    >>> someone help me write code strip the time part so only values like
    >>> "7/15/2005" will be left.
    >>>
    >>> Note - We must be able to strip dates with double digit months and
    >>> days, so i can't just use the right function with a hard coded
    >>> parameter.
    >>>
    >>> LISTING 1
    >>>
    >>> mydatetimevalue = "7/15/2005 8:00:00 AM"

    >>
    >> Use the FormatDateTime function. See:
    >> http://www.aspfaq.com/show.asp?id=2313
    >>
    >> Or are you are asking how to do it in a query? If so, you need to
    >> tell us what database you are using.
    >>
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jul 12, 2005
    #7
  8. scott

    scott Guest

    it's datetime. Below is my sproc that works fine with m/d/yyyy datebase
    values. now, if i add the time to the m/d/yyyy value in my database, my
    sproc won't recognize them.



    SPROC LISTING

    CREATE PROCEDURE mlc_CALENDAR_GetDayEvents
    /*
    Returns calendar events for a single day on team calendar

    */
    (
    @dtDate datetime
    )
    AS
    BEGIN

    SELECT *
    FROM t_events
    WHERE eventArchived = 0 AND
    eventStart_date >= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 12:00
    AM')) AND
    eventEnd_date <= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 11:59
    PM'))
    END
    GO



    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Then you'll want to read this:
    > http://www.aspfaq.com/show.asp?id=2040
    >
    > What datatype is the parameter?
    >
    > Bob Barrows
    >
    > scott wrote:
    >> I need just need some nifty use of string functions to chop off the
    >> time and leave m/d/yyyy string. it's going to a sproc as a parameter
    >> so time needs to be choped off, not just displayed right.
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:O1QU$...
    >>> scott wrote:
    >>>> I have a field with datetime values like below LISTING 1. Can
    >>>> someone help me write code strip the time part so only values like
    >>>> "7/15/2005" will be left.
    >>>>
    >>>> Note - We must be able to strip dates with double digit months and
    >>>> days, so i can't just use the right function with a hard coded
    >>>> parameter.
    >>>>
    >>>> LISTING 1
    >>>>
    >>>> mydatetimevalue = "7/15/2005 8:00:00 AM"
    >>>
    >>> Use the FormatDateTime function. See:
    >>> http://www.aspfaq.com/show.asp?id=2313
    >>>
    >>> Or are you are asking how to do it in a query? If so, you need to
    >>> tell us what database you are using.
    >>>
    >>> --
    >>> Microsoft MVP - ASP/ASP.NET
    >>> Please reply to the newsgroup. This email account is my spam trap so
    >>> I don't check it very often. If you must reply off-line, then remove
    >>> the "NO SPAM"

    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    scott, Jul 12, 2005
    #8
  9. scott

    Roland Hall Guest

    "McKirahan" wrote in message news:...
    : "scott" <> wrote in message
    : news:...
    : > I have a field with datetime values like below LISTING 1. Can someone
    help
    : > me write code strip the time part so only values like "7/15/2005" will
    be
    : > left.
    : >
    : > Note - We must be able to strip dates with double digit months and days,
    : so
    : > i can't just use the right function with a hard coded parameter.
    : >
    : >
    : > LISTING 1
    : >
    : > mydatetimevalue = "7/15/2005 8:00:00 AM"
    :
    :
    : WScript.Echo Split(mydatetimevalue," ")(0)

    You beat me too it but you don't need " " do ya'? Space is the default
    delimiter.

    WScript.Echo Split(mydatetimevalue)(0)

    --
    Roland Hall
    /* This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose. */
    Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
    WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
    MSDN Library - http://msdn.microsoft.com/library/default.asp
    Roland Hall, Jul 13, 2005
    #9
  10. > it's datetime. Below is my sproc that works fine with m/d/yyyy

    Yes, but it MIGHT NOT TOMORROW! Use a *SAFE* format, please. YYYYMMDD
    works for SQL Server,YYYY-MM-DD works for Access. Please read
    http://www.aspfaq.com/2023 -- just because something works fine does not
    make it the right thing to do.

    > now, if i add the time to the m/d/yyyy value in my database, my sproc
    > won't recognize them.


    What do you mean? Do you get an error message? If so, what is it?

    > SELECT *
    > FROM t_events
    > WHERE eventArchived = 0 AND
    > eventStart_date >= CONVERT(datetime,(convert(varchar,@dtDate,101) + '
    > 12:00 AM')) AND
    > eventEnd_date <= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 11:59
    > PM'))


    Do not use SELECT * in production code. And try the following instead, if
    you look atthe queyr plan I think you will find that it is at least as
    efficient if not more so, and is much easier to read.

    -- always name the owner!
    CREATE PROCEDURE dbo.mlc_CALENDAR_GetDayEvents
    @dt SMALLDATETIME
    AS
    BEGIN
    SET NOCOUNT ON

    -- most efficient way to strip time:
    SET @dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @dt))

    SELECT Column_Names
    FROM t_events
    WHERE eventArchived = 0
    AND eventStart_date >= @dt
    AND eventEnd_date < (@dt+1)
    -- use less then the next day at midnight
    -- then you don't lose values like 11:59:30
    END
    GO

    Now, do you really mean to only capture events that last < 24 hours? Or did
    you mean to capture events that start on @dt, end on @dt, or are in progress
    on @dt?

    A
    Aaron Bertrand [SQL Server MVP], Jul 13, 2005
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Christos TZOTZIOY Georgiou
    Replies:
    3
    Views:
    685
    Christos TZOTZIOY Georgiou
    Sep 13, 2003
  2. Tim Peters
    Replies:
    0
    Views:
    517
    Tim Peters
    Sep 9, 2003
  3. mp
    Replies:
    1
    Views:
    372
    John Machin
    Jul 28, 2006
  4. Martin
    Replies:
    0
    Views:
    325
    Martin
    Dec 27, 2008
  5. Replies:
    2
    Views:
    740
    M.-A. Lemburg
    Jan 6, 2009
Loading...

Share This Page