Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Tsql Statement

Posted on 2013-01-30
Last Modified: 2013-02-02
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?


Question by:Tech315

Accepted Solution

Nalinkumarbalaji earned 500 total points
ID: 38836265
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-%'

Expert Comment

by:Ross Turner
ID: 38836311
Maybe try something like (untested):
Left(RIGHT('20' + CONVERT(VARCHAR,'06-0325310',12),4)
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38836322
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 '??-%'
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Expert Comment

ID: 38836346
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 ...
LVL 32

Expert Comment

ID: 38836461
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,
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38837172
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
    year BETWEEN '2010' AND '2012'

If the computation for that column ever changes, you only need to change a single definition in a single place.

Author Closing Comment

ID: 38847136
This was the quickest and most easy one. Thanks

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question