January 14, 2020 at 4:25 pm
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
January 14, 2020 at 4:38 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2020 at 4:40 pm
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?
January 14, 2020 at 4:57 pm
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.
January 14, 2020 at 5:00 pm
thank you
January 14, 2020 at 5:20 pm
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);
January 14, 2020 at 6:13 pm
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
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
Change is inevitable... Change for the better is not.
January 14, 2020 at 7:28 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 14, 2020 at 8:07 pm
If you store the result in a date typed variable or column instead of VARCHAR, you could use the DATEFROMPARTS function introduced in 2012:
January 14, 2020 at 11:18 pm
>> ... 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.
January 15, 2020 at 12:58 am
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
Change is inevitable... Change for the better is not.
January 15, 2020 at 5:02 pm
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.
January 15, 2020 at 5:14 pm
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.
January 15, 2020 at 8:54 pm
>> 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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply