Solved

Tsql Statement

Posted on 2013-01-30
7
231 Views
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?

Thanks

T
0
Comment
Question by:Tech315
7 Comments
 
LVL 3

Accepted Solution

by:
Nalinkumarbalaji earned 500 total points
Comment Utility
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
 
LVL 7

Expert Comment

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

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Expert Comment

by:daamonturne
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Closing Comment

by:Tech315
Comment Utility
This was the quickest and most easy one. Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delete from table 6 36
How to query LOCK_ESCALATION 4 37
Query question 4 29
Help with SQL Query 23 39
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now