Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Tsql Statement

Hi I have this number in one column 06-03253 and need the first 2 digits to be made into a year in the column next to it ie.(06-03253 = 2006) Is this possible? If so how would I go about it?

Thanks

T
0
Tech315
Asked:
Tech315
1 Solution
 
NalinkumarbalajiCommented:
Replace (FieldName,'06-','2006-')

Before replacing check if there is any other values are coming for the select query.

Select Fieldname from tablename where Fieldname like '%06-%'
0
 
Ross TurnerCommented:
Maybe try something like (untested):
Left(RIGHT('20' + CONVERT(VARCHAR,'06-0325310',12),4)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
this should work for 2000 and 1900

SELECT CASE WHEN CAST(LEFT(urColumn,2) AS  int) between 0 and 20 then '20' else '19' end + urColumn
where urColumn like '??-%'
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
daamonturneCommented:
use an update staement to o\populate the adjacent field

      update tablename set col2 = 2000 + left(col1,2)

or if selecting try
 
      select col1,2000 + left(col1,2) as col2 ...
0
 
awking00Commented:
What is currently in the column next to it and what is its datatype? Can there be value like 98-32523 where you would want 1998 in the next column,
0
 
Scott PletcherSenior DBACommented:
You can use a computed column, so you don't even have to store the year value in a column unless you just want to.


ALTER TABLE dbo.tablename
ADD [year] AS CAST(CASE WHEN LEFT(column_name, 2) < '50' THEN '20' ELSE '19' END + LEFT(column_name, 2) AS char(4))


From then on, you can use [year] just like any other column name:


SELECT year, MAX(column_name), ...
FROM dbo.tablename
WHERE
    year BETWEEN '2010' AND '2012'
GROUP BY
    year
ORDER BY
    year


If the computation for that column ever changes, you only need to change a single definition in a single place.
0
 
Tech315Author Commented:
This was the quickest and most easy one. Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now