getting year from string
-
Hi to all,
I want to retrieve year from my database column field. The datatype of column is string and data is saving as '1/2005' where 1 is minth number and 2005 is year. Now i just want to retrive the year part from the above column...
i tried this by using
select right(month,5) as year from <table name>
this results me two answers one is 2005 in case i have single digit month number i.e. from 1-9 but in case of two digit number i.e. 10-12 this gives me /2005 where as i need only 2005.
can somebody help me out of this query..
thanks in advance!!
-
I'm guessing it's a char column and not a varchar..?
Try
select right(RTRIM(month),4) as year from <table name>
/Kenneth
-
I had a similar challange that I solved this way. In my example the field was in the format; mm/yyyy (01/2005, 07/2005, 11/2005). The year was 'extracted' by substring (function?) starting from the 4th character position for 4 characters.
select year=substring(date_field,4,4), ....
-
I would:
select
year(cast(field as datetime))
, month(cast(field as datetime))
, day(cast(field as datetime))
from <yourtable>
hth
* Noel -
Won't work. As the input 'date' is '1/2005', which isn't a complete date, you can't do any datefunctions on such a string...
/Kenneth
-
Ah... didn't read well that the string was missing the day part!
* Noel -
well.. what the question really was about was how to get the 4 last charachters (that just happened to mean 'year') from a string that could be 6 or 7 chars total length.ie 1/2005 or 11/2005 where 2005 is the wanted part...
/Kenneth
-
try this:
SELECT substring(field,CHARINDEX('/', field)+1,4) from table
-
You can use SQL's built-in DateTime functions to extract various date values such as Year() which will return the 4 digit year of a date. In your case your date is not complete in that it stores only the MM/YYYY value and it does this as a string since DateTime data type requires a complete date value. The method that will let you obtain the year from your date as it is stored and do so in in any situation is to change your date to a date value that SQL can recognize and then call the built-in Date function Year() like this:
DECLARE @sDate VARCHAR(7)
SET @sDate = '01/005'
SELECT Year(Convert(DateTime,'02/' + @sDate))
This works by taking your date and adding to it the string '01/' so that the result is a string value that SQL can recognize as a date. It doesn't matter whether the value your adding is meant to represent a month or a day because you are only concerned with the year. So if SQL interprets your date as either January Second, 2005 or February First 2005 it does not matter because the year is the same. The above requires one character concatenation, an implicit date conversion and then a function call.
The above is a recreation of your situation designed to demonstrate that the value you add to the beginning does not matter as to whether it is interpreted by SQL as a Month or as a day because the year is the same in either case. The actual syntax you would use should probably look like this:
Year(Convert(DateTime,'01/' + Table.Column))
where Table.Column is the table and column that contains you date stored as a string value.
The only thing I didn't do is make an explicit conversion call to convert the date stored as string to a DateTime value which is the data type that the Year function requires. I decided to let the query processor make the call as to the best method to handle how to pass the date as a string value to the Year function. I don't know for certain if this better in terms of performance or not. It might be that an explicit conversion to a DateTime value is faster I just don't know. I personally like to avoid string functions when possible as they are to my understanding notoriously slow. I can't say for certain that string concatenation is any better however I would think that is has to be somewhat faster then the standard string functions such as Left & Right. In any case this is just another example of how in SQL you can typically get the same result via many paths.
Kindest Regards,
Just say No to Facebook!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply