returning date with two digit year

Discussion in 'ASP General' started by middletree, Jul 8, 2005.

  1. middletree

    middletree Guest

    I have a field that is stored in SQL Server in date/time format. On one
    page, I only need the date to display. I am able to do this by saying:

    DateValue(strLastModified)

    However, this returns to me the date in this format:

    05/10/2004

    Instead, I would like to see:

    05/10/04, but I am not sure how to do this.
    middletree, Jul 8, 2005
    #1
    1. Advertising

  2. > 05/10/2004
    >
    > Instead, I would like to see:
    >
    > 05/10/04, but I am not sure how to do this.


    Can I ask why you would like either of these formats? Both are ambiguous,
    and the one you'd prefer is more so than the one you're currently using.

    05/10/2004 - is that May 10th, or October 5th? Depends on what country you
    are in/from...

    05/10/04 - this could be just about anything, May 10th 2004, October 5th
    2004, October 4th 2005...

    Anyway, of course it is trivial to create your own functions that format the
    date however you want it. Your specific format isn't offered, but it should
    take about four seconds to adapt one of the samples:

    http://www.aspfaq.com/2313

    Personally, I try to stick with YYYY-MM-DD for all display values, and
    YYYYMMDD for all values that are passed to a database.

    A
    Aaron Bertrand [SQL Server MVP], Jul 8, 2005
    #2
    1. Advertising

  3. middletree

    middletree Guest

    Good questions. I am following the orders of my boss, and this is an
    Intranet-based app, so only certain people will see it. There's one page
    that the boss wants me to squeeze several columns on, yet he still wants it
    narrow enough to fit onto the screen without scrolling.

    I should add that before I post a question here, I do look elsewhere,
    including your site and Microsoft, plus my MSDN library. But I didn't find
    any pre-made function. I figured it fhter was one, I'd hear about it here,
    and if not, I'd fiddle with the Right function.



    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:...
    > > 05/10/2004
    > >
    > > Instead, I would like to see:
    > >
    > > 05/10/04, but I am not sure how to do this.

    >
    > Can I ask why you would like either of these formats? Both are ambiguous,
    > and the one you'd prefer is more so than the one you're currently using.
    >
    > 05/10/2004 - is that May 10th, or October 5th? Depends on what country

    you
    > are in/from...
    >
    > 05/10/04 - this could be just about anything, May 10th 2004, October 5th
    > 2004, October 4th 2005...
    >
    > Anyway, of course it is trivial to create your own functions that format

    the
    > date however you want it. Your specific format isn't offered, but it

    should
    > take about four seconds to adapt one of the samples:
    >
    > http://www.aspfaq.com/2313
    >
    > Personally, I try to stick with YYYY-MM-DD for all display values, and
    > YYYYMMDD for all values that are passed to a database.
    >
    > A
    >
    >
    middletree, Jul 8, 2005
    #3
  4. middletree

    middletree Guest

    "Aaron Bertrand [SQL Server MVP]" <> wrote in message

    > Your specific format isn't offered, but it should
    > take about four seconds to adapt one of the samples:
    >
    > http://www.aspfaq.com/2313
    >
    >


    Hmm. I saw this article the first time around, but didn't really understand
    how to use the sample code. For the format I want, I found this:

    <%
    response.write pd(DAY(date()),2) & "-" & _
    pd(MONTH(date()),2) & "-" & _
    pd(RIGHT(YEAR(date()),2),2)
    %>

    Of course, just need to replace the hyphen with a slash. But I don't really
    get how to use this code. Does it require the code snippet at the top of the
    page?
    middletree, Jul 8, 2005
    #4
  5. middletree wrote:
    > I have a field that is stored in SQL Server in date/time format. On
    > one page, I only need the date to display. I am able to do this by
    > saying:
    >
    > DateValue(strLastModified)
    >
    > However, this returns to me the date in this format:
    >
    > 05/10/2004
    >
    > Instead, I would like to see:
    >
    > 05/10/04, but I am not sure how to do this.


    SELECT RecordID,
    CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate,
    ...
    FROM MyTable

    http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
    Dave Anderson, Jul 8, 2005
    #5
  6. middletree

    Evertjan. Guest

    middletree wrote on 08 jul 2005 in microsoft.public.inetserver.asp.general:

    > this returns to me the date in this format:
    >
    > 05/10/2004
    >
    > Instead, I would like to see:
    >
    > 05/10/04, but I am not sure how to do this.


    The old straightforward basic way:

    d = "05/10/2004"

    d2 = left(d,6) & right(d,2)


    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Jul 8, 2005
    #6
  7. middletree

    middletree Guest

    Oh wow. That is pretty basic. I never even thought of that. Thanks!


    "Evertjan." <> wrote in message
    news:Xns968E3063224eejj99@194.109.133.242...
    > middletree wrote on 08 jul 2005 in

    microsoft.public.inetserver.asp.general:
    >
    > > this returns to me the date in this format:
    > >
    > > 05/10/2004
    > >
    > > Instead, I would like to see:
    > >
    > > 05/10/04, but I am not sure how to do this.

    >
    > The old straightforward basic way:
    >
    > d = "05/10/2004"
    >
    > d2 = left(d,6) & right(d,2)
    >
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Replace all crosses with dots in my emailaddress)
    >
    middletree, Jul 11, 2005
    #7
  8. > But *WHY* would you do that when SQL already has the ability to give you
    > the date in the format you desire?


    Because many would argue that string formatting is the job of the
    client/presentation tier, not the database.
    Aaron Bertrand [SQL Server MVP], Jul 11, 2005
    #8
  9. middletree

    Evertjan. Guest

    Dave Anderson wrote on 11 jul 2005 in
    microsoft.public.inetserver.asp.general:

    > middletree wrote:
    >>> d2 = left(d,6) & right(d,2)

    >>
    >> Oh wow. That is pretty basic. I never even thought of that. Thanks!

    >
    > But *WHY* would you do that when SQL already has the ability to give
    > you the date in the format you desire?
    >


    Because, Dave, one should have alternatives to choose from.

    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Jul 11, 2005
    #9
  10. middletree

    middletree Guest

    I could see both sides, but ultimately, I see it the way Aaron put it. The
    presentation layer is the presentation layer.

    Having said that, you provided me with a bit of SQL code that I had not been
    aware of, so I thank you. I am sure it will come in handy down the road.


    "Dave Anderson" <> wrote in message
    news:...
    > middletree wrote:
    > >> d2 = left(d,6) & right(d,2)

    > >
    > > Oh wow. That is pretty basic. I never even thought of that. Thanks!

    >
    > But *WHY* would you do that when SQL already has the ability to give you

    the
    > date in the format you desire?
    >
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.

    Use
    > of this email address implies consent to these terms. Please do not

    contact
    > me directly or ask me to contact you directly for assistance. If your
    > question is worth asking, it's worth posting.
    >
    >
    middletree, Jul 11, 2005
    #10
  11. middletree

    middletree Guest

    "Dave Anderson" <> wrote in message
    news:...
    > SELECT RecordID,
    > CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate,
    > ...
    > FROM MyTable
    >
    > http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp



    I actually tried this and got an error; I'm reading through the doc you
    linked me to, but thought I'd run it by you:

    Here's the query, using response.write:

    SELECT T.LastModified CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate FROM
    TKT_TICKET T WHERE TKT_TicketID = 16056

    Note that LastModified is a time/date field, so the value it is starting
    with is 2004-09-16 11:37:14.000



    Here's the error, which I got both in the browser and then in the Query
    Analyzer:

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'CONVERT'.
    middletree, Jul 11, 2005
    #11
  12. middletree

    middletree Guest

    Actually, this is not working I think it's because some dates have a 0 in
    front of them and some don't. I don't know why this is, but I'll have to
    track it down.


    "Evertjan." <> wrote in message
    news:Xns9690A1F2A2ADDeejj99@194.109.133.242...
    > Dave Anderson wrote on 11 jul 2005 in
    > microsoft.public.inetserver.asp.general:
    >
    > > middletree wrote:
    > >>> d2 = left(d,6) & right(d,2)
    > >>
    > >> Oh wow. That is pretty basic. I never even thought of that. Thanks!

    > >
    > > But *WHY* would you do that when SQL already has the ability to give
    > > you the date in the format you desire?
    > >

    >
    > Because, Dave, one should have alternatives to choose from.
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Replace all crosses with dots in my emailaddress)
    >
    middletree, Jul 11, 2005
    #12
  13. > SELECT T.LastModified CONVERT(CHAR(8),EffectiveDate,1) AS EffectiveDate

    Not sure how this is Dave's fault. You need commas between
    column/expression names. Would you say:

    SELECT T.LastModified T.AnotherColumn T.YetAnotherColumn FROM Table T

    Or should it be

    SELECT T.LastModified, T.AnotherColumn, T.YetAnotherColumn FROM Table T

    Also, if you ned the alias T on LastModified, why not also in the
    EffectiveDate in the expression? Here's how I would write it, using
    carriage returns between column names helps to identify these issues as
    well.

    SELECT
    T.LastModified,
    EffectiveDate = CONVERT(CHAR(8),EffectiveDate,1)
    FROM
    ...

    And finally, USE A STORED PROCEDURE.
    Aaron Bertrand [SQL Server MVP], Jul 11, 2005
    #13
  14. > SELECT
    > T.LastModified,
    > EffectiveDate = CONVERT(CHAR(8),EffectiveDate,1)
    > FROM


    Sorry,

    SELECT
    T.LastModified,
    EffectiveDate = CONVERT(CHAR(8),T.EffectiveDate,1)
    FROM

    (Or, if you are only selecting from one table, drop the alias altogether.)
    Aaron Bertrand [SQL Server MVP], Jul 11, 2005
    #14
  15. middletree

    middletree Guest

    I didn't mean to say it was his fault.

    Here's what worked:
    SELECT CONVERT(CHAR(8),T.LastModified,1) AS EffectiveDate FROM TKT_TICKET T
    WHERE T.TKT_TicketID = 16056


    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:...
    > > SELECT
    > > T.LastModified,
    > > EffectiveDate = CONVERT(CHAR(8),EffectiveDate,1)
    > > FROM

    >
    > Sorry,
    >
    > SELECT
    > T.LastModified,
    > EffectiveDate = CONVERT(CHAR(8),T.EffectiveDate,1)
    > FROM
    >
    > (Or, if you are only selecting from one table, drop the alias altogether.)
    >
    >
    middletree, Jul 11, 2005
    #15
  16. middletree

    Evertjan. Guest

    middletree wrote on 11 jul 2005 in
    > "Evertjan." wrote in message
    >> Dave Anderson wrote on 11 jul 2005 in
    >>
    >> > middletree wrote:
    >> > > "Evertjan." wrote in message


    >> >>> > 05/10/2004
    >> >>> >
    >> >>> > Instead, I would like to see:
    >> >>> >
    >> >>> > 05/10/04, but I am not sure how to do this.


    >> >>> d2 = left(d,6) & right(d,2)
    >> >>
    >> >> Oh wow. That is pretty basic. I never even thought of that.
    >> >> Thanks!
    >> >
    >> > But *WHY* would you do that when SQL already has the ability to
    >> > give you the date in the format you desire?
    >> >

    >>
    >> Because, Dave, one should have alternatives to choose from.

    >
    > Actually, this is not working I think it's because some dates have a 0
    > in front of them and some don't. I don't know why this is, but I'll
    > have to track it down.


    [Please do not toppost on usenet]

    Re: oldfasioned Basic

    I only supplied code as per your specifications 05/10/04

    If the leading zeros could be missing, try:

    d = "05/10/2004"
    a = split(d,"/")
    d2 = a(0) & "/" & a(1) & "/" & right(a(2),2)

    and if you want to reinstate those leading zeros:

    function two(x)
    two = right("0" & x,2)
    end function

    d = "05/10/2004"
    a = split(d,"/")
    d2 = two(a(0)) & "/" & two(a(1)) & "/" & two(a(2))

    Programming is a game, an art, and a way to joke.

    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Jul 11, 2005
    #16
  17. Why do you have this desire to force everyone to post the way you do? Do
    you like scrolling through irrelevant and redundant text so much that you
    think everyone on "usenet" should have to do it?


    > [Please do not toppost on usenet]
    Aaron Bertrand [SQL Server MVP], Jul 11, 2005
    #17
  18. middletree

    Evertjan. Guest

    Aaron Bertrand [SQL Server MVP] wrote on 11 jul 2005 in
    microsoft.public.inetserver.asp.general:

    > Why do you have this desire to force everyone to post the way you do?
    > Do you like scrolling through irrelevant and redundant text so much
    > that you think everyone on "usenet" should have to do it?
    >
    >> [Please do not toppost on usenet]


    Next to the many excellent ideas you have, Aaron,
    you have a strange idea of the word "force".

    Usually, when I force someone, I don't say "please".

    --
    Evertjan.
    The Netherlands.
    (Replace all crosses with dots in my emailaddress)
    Evertjan., Jul 11, 2005
    #18
  19. Okay. Please stop asking everyone and their brother to post the way you
    want them to. And please stop including irrelevant text that requires more
    scrolling if you are going to continue bottom-posting.



    > Next to the many excellent ideas you have, Aaron,
    > you have a strange idea of the word "force".
    >
    > Usually, when I force someone, I don't say "please".
    Aaron Bertrand [SQL Server MVP], Jul 11, 2005
    #19
  20. middletree

    middletree Guest

    I never know what format to use. I hate bottom-posting, and most people I
    know agree with me. But whatever the local rules are, I'll try and follow
    'em. At any rate, thanks for the amended code.
    middletree, Jul 11, 2005
    #20
    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. Fangs
    Replies:
    3
    Views:
    9,767
    darshana
    Oct 26, 2008
  2. =?Utf-8?B?UGFuYXlvdGlzIEtvdXZhcmFraXM=?=

    2-digit year dates not validated properly during client validation

    =?Utf-8?B?UGFuYXlvdGlzIEtvdXZhcmFraXM=?=, Dec 2, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    533
    =?Utf-8?B?UGFuYXlvdGlzIEtvdXZhcmFraXM=?=
    Dec 2, 2005
  3. Otuatail

    Week of year to full Year

    Otuatail, Dec 8, 2003, in forum: HTML
    Replies:
    4
    Views:
    956
    Toby A Inkster
    Dec 8, 2003
  4. Veerle
    Replies:
    2
    Views:
    397
    niceguy
    Aug 20, 2003
  5. David Woodward
    Replies:
    5
    Views:
    369
    Dr John Stockton
    Feb 2, 2004
Loading...

Share This Page