Update with a month name 1

[Cpreston]

HI

I have a small query. The name column which is varchar returns 01,02,03 etc.... I want to replace if 01 to say Jan, If 02 to say Feb etc....
How can achieve this please. Any ideas, many thanks in advance

SELECT        asset, name AS Month, MoulderEvents, ModTime, PLEvents, PLTime, OverrunEvents, OVRTime
FROM            [PLANTRUN-ABF\SQLEXPRESS].prodigy_data_quickstats_2023.dbo.reasoncodes2023cp AS reasoncodes2023cp_1

 

[Andrzejek]

Google is your friend...

You have several ways of how to Convert Month Number to Month Name Function in SQL here.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

I have tried several from the link you sent and from google but I cant find anything that uses the field name and if it is 01 put January or 02 February etc...

 

[SkipVought]

I'm guessing that your table has FIELD NAMES that are "01", "02"...

If that's the case, you must convert "01" to 1 and so on to use in the functions you're looking for.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

This (from link provided):[tt][blue]
select FORMAT(DATEFROMPARTS(1900, CAST([highlight #FCE94F]'02'[/highlight] AS INT), 1), 'MMM', 'en-US')[/blue][/tt]
gives you [blue][tt]Feb[/tt][/blue]

You may replace [highlight #FCE94F]highlighted portion[/highlight] with the name of your field.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Thanks for the replies very useful as always, this one did the trick with the 02 changed to the field name
select FORMAT(DATEFROMPARTS(1900, CAST('02' AS INT), 1), 'MMM', 'en-US')

 

[Chris Miller]

Just curious, Andrzejek,

SELECT FORMAT(DATEFROMPARTS(1900, '02', 1), 'MMM', 'en-US');
SELECT FORMAT(DATEFROMPARTS(1900,   2 , 1), 'MMM', 'en-US');

Why does this work both with character and integer data type in the SQLFiddle?

http://sqlfiddle.com/#!18/9eecb/181117

A screenshot of that after running, just to prove the point:

In other words, Andrzejek, is there a setting that makes an SQL Server instance more or less strict about implicit vs explicit data type conversions?

There is no doubt that the documentation about DATEFROMPARTS (

https://learn.microsoft.com/en-us/s...fromparts-transact-sql?view=sql-server-ver16)

tells the month has to be an integer parameter from 1 to 12 and is not talking about implicit conversions. But is there a general topic that describes when and how implicit conversions are done and can be turned on or off? Or is it even just depending on the instance version or the database COMPATIBILITY_LEVEL?

PS: Also tried a varchar variable and field and it still works.

Chriss

 

[gmmastros]

There is an article that talks about how implicit conversions are done. It can be found here:

https://learn.microsoft.com/en-us/s...precedence-transact-sql?view=sql-server-ver16

There is also a SQL_VARIANT_PROPERTY function that can give you information regarding the data type of a variable;

https://learn.microsoft.com/en-us/s...sql?redirectedfrom=MSDN&view=sql-server-ver16

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chris Miller]

Okay, I fear I haven't looked into this enough to connect this to why this would do an implicit type conversion in same cases and instead throws an error. But maybe that assumpton is wrong.

Chriss

 

[gmmastros]

We're in the weeds now.

Implicit data type conversions can cause weird, previously unknown problems to surface. Based on my previous post, it should be clear that when an implicit conversion exists between string (varchar et al) and number, sql server prefers to convert to number. This is a shame because all numbers can be converted to a string, but not all strings can be converted to numbers.

Declare @Temp Table(value varchar(10))

Insert into @temp Values(1)
Insert into @temp Values(2)
Insert into @temp Values(3)
Insert into @temp Values(4)
Insert into @temp Values(5)
Insert into @temp Values(11)

Select * From @Temp Where Value > 2
-- 
-- Value
-- 3
-- 4
-- 5
-- 11

Select * From @Temp Where Value > [!]'[/!]2[!]'[/!]
-- 
-- Value
-- 3
-- 4
-- 5

It's clear that the value column is a varchar(10). It should also be clear that we are inserting integers into that column. Looking at the last line, you might think the query would fail, but if you run this, you will see that it actually does exactly what you think it would.

The first select query (the one without the single quotes around the 2) returns exactly the same results that would be returned if the column was an integer, instead of a varchar. It does this because the where clause is treating the value column like an integer. Specifically, since you are comparing a varchar and an int, the implicit conversion will actually convert the string (in the table) to an int for the purposes of the comparison.

The second query is comparing a string with a string which is why '11' is less than '2'.

More fun? sure. Using the code above, add these 2 lines to the bottom and run it.

Insert Into @temp Values('six')

Select * From @Temp Where Value > 2

The insert works just fine because the column data type is a varchar, and we're inserting a varchar. However, notice that we are running the exact same query that we previously ran. Now you'll get an error.

[!]
Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'six' to data type int.[/!]

The error message makes it obvious that every value in the table is getting converted to an integer for the purposes of the where clause. Not only can this lead to weird errors all of a sudden, but it can also cause performance problems with very large tables.

Bottom line, developers should always be careful with data types. It's easy to overlook them, so it can be difficult to do them consistently correct across all the queries in your database.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom