Changing the format for a date

  • Comments posted to this topic are about the item Changing the format for a date

  • question is incorrect - but I guessed what you wanted.

  • It should be:

    select CONVERT(varchar(10), @d, 104)

  • DECLARE @d DATETIME = '2021/02/22'

    Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    That ain't going to work for all LANGUAGE 🙂

    ilyaioffe wrote:

    It should be:

    select CONVERT(varchar(10), @d, 104)

    Might be some debate about whether it should be CHAR ...

    DECLARE @d DATETIME = '20210222'
    SELECT CONVERT(CHAR(10), @d, 104)

     

  • No response is correct, I agree with ilyaioffe

  • Good I' m not the only one that I noticed this 🙂

  • Thank you people - thought I'd lost the plot for a minute there.  Doesn't anyone review these questions before they are published?

    At the end of most articles is a small blurb called a person's signature which exists to provide information about how to get in touch with the person posting, including their email address, phone number, address, or where they're located. Signatures have become the graffiti of computers. People put song lyrics, pictures, philosophical quotes, even advertisements in them. (Note, however, that advertising in your signature will more often than provoke negative responses until you take it out.)

  • chris.mackenzie 149 wrote:

    Doesn't anyone review these questions before they are published?

    Surely the point of questions like these is to deliberately raise issues that make people think and try things out and thereby increase their knowledge ... that's how I have learnt over the years

  • If the question is wrong, how do you even pick an answer?!?

    • This reply was modified 1 week, 2 days ago by  Toni-256719.
  • Given that CAST only has one parameter, I chose the CONVERT solution, even though once I tried it I found it doesn't give the desired result.

    As others stated, the first parameter should be char(10) or varchar(10).  In this instance using char or varchar without a length works as well, but we really shouldn't get into that bad habit.

     

  • Toni-256719 wrote:

    If the question is wrong, how do you even pick an answer?!?

    I must be missing something because I cannot find fault with the question:

    How can I get this in the format dd.mm.yyyy and returned to the client as a string?

    The example data does not work for some LANGUAGE settings, but it is absolutely clear what the sample data is intended to be

    "None of the above" was not an available answer ... but all the various proposed answers hint at what the correct solution is, so provide food-for-thought.

  • You are correct.  Sorry.  I tried to remove my comment but was unable to.

    • This reply was modified 1 week, 2 days ago by  Toni-256719.
  • GaryV wrote:

    In this instance using ... varchar without a length works as well, but we really shouldn't get into that bad habit. 

    Definitely a bad habit 🙂 ... the resulting data without a SIZE is 30 characters. Of course that is big enough for a date formatted to be 10 characters 🙂 ... but ...

    SELECT CONVERT(varchar, @d, 104)

    ... if that data is then further-processed the assigned width will be 30 char rather than 10. That sort of thing leads to bugs downstream which are then very hard-to-find.

    varchar used, without size, in a DECLARE statement has size = 1 - although maybe that is likely to cause trouble immediately! and thus come to light, whereas a default CAST / CONVERT size of 30 for Date, Int, etc. is "big enough" to go unnoticed most of the time (but not GUID)

    I think the fault here is that SQL doesn't have a "STRICT" parameter which would make it possible to "disallow" such shortcuts which are hangovers from legacy decades ago.

    Here's another that I hate and would like to have the option of a STRICT setting to disallow:

    SELECT	Col1 AS MyCol1,
    Col2 AS MyOtherColName,
    Col3
    Col4

    largely because of the risk of accidentally typing that, the risk of which increases with very long lines, or multiple line statements, we use this style of formatting such that typo-errors are far more visible to the programmer

    SELECT	Col1 AS MyCol1
    , Col2 AS MyOtherColName
    , Col3
    , Col4

    Actually our preference (again because of the risk of an "AS" being at the end of a long / multi-line statement) is

    SELECT	[MyCol1] = Col1
    , [MyOtherColName] = Col2
    , Col3
    , Col4

    but of course code formatting is up to the user, all the formats are valid, my own advice would be "be 100% consistent"

  • Apologies. Editing the answer created a typo here. This has been corrected.

  • It is a much better practice to use FORMAT rather than CONVERT as you can specify the required format, i.e. 'dd.mm.yyyy' rather than some magic number that no one can remember what it converts too.

Viewing 15 posts - 1 through 15 (of 16 total)

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