tyring to convert a varchar to a Date

  • itmasterw 60042

    SSCrazy

    Points: 2728

    Hi

    I have the following code:

    in the end, the date should look like 1/4/2020, in a date format. And while this appears to be doing that, it comes back with an error.

    Msg 9807, Level 16, State 0, Line 9

    The input character string does not follow style 100, either change the input character string or use a different style.

    I tried different styles but I can not seem to get it without the error. Any ideas would be appraicated.

    Thank you

    DECLARE @dateFull varchar(12) 
    DECLARE @dateFullDay Varchar(2) = DATEPART(d,GETDATE()-10)
    SELECT @dateFullDay
    DECLARE @dateFullMonth Varchar(2) = DATEPART(m,GETDATE())
    SELECT @dateFullMonth
    DECLARE @dateFullYear Varchar(4) = DATEPART(yyyy,GETDATE())
    SELECT @dateFullYear
    SET @dateFull = @dateFullMonth + '/' + @dateFullDay+ '/' + @dateFullYear
    SET @dateFull = CONVERT(date, @dateFull,100)
    SELECT @dateFull
  • Phil Parkin

    SSC Guru

    Points: 243910

    Like this?

    SELECT CONVERT(VARCHAR(10), GETDATE(), 101);

    Formatting of DATE and DATETIME columns for cosmetic reasons should be left to the client & not performed in SSMS.

    • This reply was modified 3 days, 13 hours ago by  Phil Parkin.
    • This reply was modified 3 days, 13 hours ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ZZartin

    SSC-Dedicated

    Points: 30412

    Wait what are you trying to do?  When you say the date should look like something that means you do want it in varchar format, why are you trying to force it back into a date?

  • DesNorton

    SSC-Insane

    Points: 22900

    Take a look at the SQL CONVERT function for formatting the dates for display.

    But PLEASE PLEASE PLEASE do not save the dates as varchar.  You will be opening yourself up to a world of hurt later.

    • This reply was modified 3 days, 13 hours ago by  DesNorton.
  • itmasterw 60042

    SSCrazy

    Points: 2728

    That is what I have and I did look it up and it said that using 100 or 101 should give me mm/dd/yyyy format. and it is working but I get the error.

  • itmasterw 60042

    SSCrazy

    Points: 2728

    thank you

  • DesNorton

    SSC-Insane

    Points: 22900

    You are formatting your input string with style 101, and then asking SQL to interpret it as style 100.

    You need to match your style to your input string

    SELECT Style100_Success = CONVERT(date, 'Jan 4 2020', 100);

    SELECT Style100_Fail = CONVERT(date, '1/4/2020', 100);
  • Jeff Moden

    SSC Guru

    Points: 995457

    itmasterw 60042 wrote:

    Hi

    I have the following code:

    in the end, the date should look like 1/4/2020, in a date format. And while this appears to be doing that, it comes back with an error.

    Msg 9807, Level 16, State 0, Line 9

    The input character string does not follow style 100, either change the input character string or use a different style.

    I tried different styles but I can not seem to get it without the error. Any ideas would be appraicated.

    Thank you

    DECLARE @dateFull varchar(12) 
    DECLARE @dateFullDay Varchar(2) = DATEPART(d,GETDATE()-10)
    SELECT @dateFullDay
    DECLARE @dateFullMonth Varchar(2) = DATEPART(m,GETDATE())
    SELECT @dateFullMonth
    DECLARE @dateFullYear Varchar(4) = DATEPART(yyyy,GETDATE())
    SELECT @dateFullYear
    SET @dateFull = @dateFullMonth + '/' + @dateFullDay+ '/' + @dateFullYear
    SET @dateFull = CONVERT(date, @dateFull,100)
    SELECT @dateFull

    If all you're trying to do is convert

    itmasterw 60042 wrote:

    Hi

    I have the following code:

    in the end, the date should look like 1/4/2020, in a date format. And while this appears to be doing that, it comes back with an error.

    Msg 9807, Level 16, State 0, Line 9

    The input character string does not follow style 100, either change the input character string or use a different style.

    I tried different styles but I can not seem to get it without the error. Any ideas would be appraicated.

    Thank you

    DECLARE @dateFull varchar(12) 
    DECLARE @dateFullDay Varchar(2) = DATEPART(d,GETDATE()-10)
    SELECT @dateFullDay
    DECLARE @dateFullMonth Varchar(2) = DATEPART(m,GETDATE())
    SELECT @dateFullMonth
    DECLARE @dateFullYear Varchar(4) = DATEPART(yyyy,GETDATE())
    SELECT @dateFullYear
    SET @dateFull = @dateFullMonth + '/' + @dateFullDay+ '/' + @dateFullYear
    SET @dateFull = CONVERT(date, @dateFull,100)
    SELECT @dateFull

    What is the purpose of the -10 thing in one spot but not including it in others?

    Also, what are you actually starting out with?  Are you simply trying to convert some string representation of some date of a known format to some other format?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98285

    Don't limit yourself to a single format.  Just CAST it to date: if it's a valid date in any format, you'll get a date back.  If not, you'll have to humanly look at it to try to figure out a valid date anyway.

    SELECT input_char_string, TRY_CAST(input_char_string AS date) AS date_string
    FROM ( VALUES(CAST('Jan 4 2020' AS varchar(30))),('1/4/2020'),
    ('08nov2016'), ('jan 14'), ('01/2004'), ('1/1/11'), ('9/3/51') )
    AS test_data(input_char_string)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Chris Harshman

    SSC-Forever

    Points: 41904

    If you store the result in a date typed variable or column instead of VARCHAR, you could use the DATEFROMPARTS function introduced in 2012:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sqlallproducts-allversions

     

  • jcelko212 32090

    SSCrazy Eights

    Points: 8906

    >> ... the date should look like 1/4/2020, in a date format. <<

    Actually, the ANSI/ISO Standards say that it ought to look like "2020-01-04", or maybe "2020-04-01"; you've picked one of the most ambiguous local dialects for date display. It is also not allowed in standard SQL.

    I hope you're not actually trying to keep dates as character strings. We went out of our way to put temporal datatypes into this language, and we expect you to use them. If you really like character strings, then use 1960s COBOL.

    Scott Pletcher suggestion to use the CAST() function is a good place to start until you can correct your DDL and fire the guy who screwed up your project like this.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 995457

    jcelko212 32090 wrote:

    Actually, the ANSI/ISO Standards say that it ought to look like "2020-01-04", or maybe "2020-04-01";

    Not quite right.  The ISO standards state that it can look like that as a secondary method with agreement between the sending and receiving parties..  The primary format to be used is stated as yyyymmdd without any punctuation.  I'll also remind you that, for example, if the "language" that SQL Server is set to is French (and others as well), the format your dates in the quote above is actually based on yyyy-dd-mmn instead of yyyy-mm-dd.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 8906

    I'm talking about the ANSI ISO Standard SQL. We debated allowing other ISO – 8601 display formats and settled on "yyyy-mm-dd" as the one and only to use. Our reasoning on the committee was that it maintains its sorted order, and can be parsed and immediately see that it is not a numeric value.

    The other parts of the ISO display format that I really like are the weekly date ("yyyyWww-d", where we see the year, a W is punctuation, the number of the week (01 to 52 or 53) within the year, dash is punctuation, and the number of the day within the week (1=monday, 7=sunday), and the ordinal date (yyyy-ddd, where the day is between 001 and 365 or 366). It's also very handy to include an ordinal business day number in a calendar table, to make calculations easier.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Lynn Pettis

    SSC Guru

    Points: 442205

    jcelko212 32090 wrote:

    I'm talking about the ANSI ISO Standard SQL. We debated allowing other ISO – 8601 display formats and settled on "yyyy-mm-dd" as the one and only to use. Our reasoning on the committee was that it maintains its sorted order, and can be parsed and immediately see that it is not a numeric value.

    The other parts of the ISO display format that I really like are the weekly date ("yyyyWww-d", where we see the year, a W is punctuation, the number of the week (01 to 52 or 53) within the year, dash is punctuation, and the number of the day within the week (1=monday, 7=sunday), and the ordinal date (yyyy-ddd, where the day is between 001 and 365 or 366). It's also very handy to include an ordinal business day number in a calendar table, to make calculations easier.

     

    All I hear when you talk anymore is the teacher from Charlie Brown cartoons.

    Guess what, Joe, COBOL is still alive and well today.  Get off your COBOL bashing.

     

  • jcelko212 32090

    SSCrazy Eights

    Points: 8906

    >> All I hear when you talk anymore is the teacher from Charlie Brown cartoons.<<

    Perhaps you want to take the wax out of your ears. You have a chance to get information from and ask questions of someone who wrote the ANSI/ISO standards for the language in which you are working. I still regret not crashing classes by Edgar Dijkstra at the University of Texas when I moved here.

    >> Guess what, Joe, COBOL is still alive and well today. Get off your COBOL bashing. <<

    I do not bash COBOL; I am well aware, there are several billion lines of it still in use today. In fact, I kid my younger friends (persons under 40) who brag about whatever the new language du jour they are using is actually nothing more than a COBOL front-end to get things on the cell phones, or whatever. Back in my glory days,no it'soh no what's going ondamn I was a FORTRAN programmer, but I used and learned enough COBOL to modify a timekeeping/payroll program used at Georgia Tech. We were using a CDC 6000 machine that converted the COBOL picture specs into the CDC format floating-point, did the math and reconverted it back faster than the IBM hardware could natively.

    The reason you think I'm bashing COBOL is that you don't understand what I'm trying to say. If you want to write COBOL, then right COBOL with its grammar conventions and data model. But don't use those conventions and data models for SQL.

    It's kind of like trying to speak German using the syntax and grammar rules of Japanese. Neither languages "wrong", but they are so totally different that they have their own domains. The result is not only inefficiency but incoherency.

    When I taught college, for a few decades, I could tell the native language of my students by their programming. I actually did an article on it for the Journal of Structured Programming long ago. If you read from right to left, then the column on which your data is sorted tends to be on the right-hand side. My Chinese students tended to put the exceptions into the if clause, and the regular processing and that then clause of a selection construct. Westerners assumed that the if clause would handle the normal operation. Somewhere long ago I made a list of about 20 or 25 examples of code differences caused by the linguistic model of the programmer.

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

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

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