Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Unanswered: Return formatted date from stored proc?

    What is the recommended method of returning a formatted date from a stored procedure?


    The date is held in a date time field. I wish to return the date formatted as:

    dd/mm/yyyy hh:mm

    for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:

    dd/mm/yyyy hh:mm:ss

    I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.

    I was looking at:

    select jobHeaders.DateTimeJobTaken AS [Job Taken],
    CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
    CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
    CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))

    from jobHeaders

    but this gives :
    8 /3 /2004 with spaces.

    Before looking further I thought one of you guys may have the answer.

    Thanks in advance
    -dw

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you want time or not?

    SELECT CONVERT(varchar(26),GetDate(),103)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    12
    Date and time without seconds

    i.e.

    dd/mm/yyyy hh:mm

    103 just gives date. I can't find a value that gived date and time (without seconds) or time only (hh:mm)

    Tnx
    Last edited by liffey; 03-08-04 at 11:48.
    -dw

  4. #4
    Join Date
    Jan 2004
    Posts
    12
    Got it!

    CONVERT(varchar(26),jobHeaders.DateTimeJobTaken,10 3) + ' ' +
    SUBSTRING(CONVERT(varchar(26),jobHeaders.DateTimeJ obTaken,108),1,5)

    or better again
    CONVERT(varchar(10),jobHeaders.DateTimeJobTaken,10 3) + ' ' +
    CONVERT(varchar(5),jobHeaders.DateTimeJobTaken,108 )


    Is there a better way?



    Thnaks for the pointer.
    -dw

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Aint this bizzare...

    SELECT CONVERT(varchar(26),GetDate(),131)


    What does anyone else get?

    I get

    17/01/1425 11:02:18:327AM
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    12
    I get the same (time excluded)


    17/01/1425 4:07:09:840PM
    -dw

  7. #7
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    17/01/1425 11:43:23:937AM

    Does this mean I will have to set my watch back 579 years if I visit Kuwait?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check this out...

    Code:
    DECLARE @x varchar(25), @y datetime
     SELECT @x = CONVERT(Varchar(25),GetDate(),131)
     SELECT @x
     SELECT @y = @x
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    How about this.

    DECLARE @x Float, @y Float
    SELECT @x = CONVERT(Float, GetDate(), 100)
    SELECT @y = CONVERT(Float, GetDate(), 131)
    SELECT @x, @y

    The internal representations match. Could this be a bug? Nah! Not in an MS product!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Like I said...it' a M$ undocumented feature....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Mar 2004
    Posts
    45
    Is not bug. Is just presentation choice. To convert back to datetime one must specify style also.

    SELECT @y = CONVERT(datetime,@x,131)

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The third parameter applies only when you convert to char/nchar/varchar/nvarchar datatypes. In you case it was ignored.

  13. #13
    Join Date
    Mar 2004
    Posts
    45
    Originally posted by rdjabarov
    The third parameter applies only when you convert to char/nchar/varchar/nvarchar datatypes. In you case it was ignored.
    Sorry you are wrong. Try this from examples above:

    DECLARE @x varchar(25), @y datetime
    SELECT @x = CONVERT(Varchar(25),GetDate(),131)
    SELECT @x
    SELECT @y = CONVERT(datetime,@x,131)
    SELECT @y

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmmm, Sorry, but I am not wrong. The statement above pertains to converting FROM datetime TO anything other than the data types mentioned. Your example demonstrates the usage of the 3rd parameter in conversion FROM varchar TO datetime, where the 3rd parameter actually plays an important part.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by HansVE
    Is not bug. Is just presentation choice. To convert back to datetime one must specify style also.

    SELECT @y = CONVERT(datetime,@x,131)

    [Thud]
    Really? How do you code a predicate?
    [/thud]*


    * Me falling off my barstool...umm office chair....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •