How to get month value from DDMMYY format?

Hi, you can use Datepart to extract the day, month, year, although you have to first format your literal first.

So assuming you have a literal of '260713', you want to change it to YYYYMMDD format. There might be a better way to parse it, but this is what I did.

declare @var varchar(max)
set @var= '260713'
set @var= '20'+Substring(@var,5,2) + Substring(@var, 3,2) + Substring(@var,1,2)

I added the 20 in front so it becomes a four letter year --> 2013
Now running the following:

select @var
select Datepart(month, @var)
select Datepart(year, @var)
select Datepart(day, @var)

which will give you:

20130726
7
2013
26

Hope this meets your requirements!

First off, don't store date values as text strings: always convert them to DateTime vlaues are store those instead - preferably before they get anywhere near SQL. This has two distinct advantages:
1) The culture sensitive part of the date input can be handled as close to the source as possible: so there is a much better chance of having access to the culture of the input computer, and thus getting the date right. For example, what date is 010203? 3rd Feb 2001? 2nd Jan 2003? 1st Feb 2003? All of those are valid, and you will find PC's set to use them sitting next to each other in some companies... Once the data gets to SQL, the culture info is lost, and it has to "guess".
2) Using DateTime columns in databases is much easier to work with when you want to do comparisons: you have the DATEPART function which extracts the various day of month, month of year etc., as you require, and it is already a numeric value which means that comparisons work as expected. If you use strings, then you can get some very odd results when you compare legitimate looking values.

Hi Cherry, here are a several ways to handle this problem. First, you have a solution there, although it will be on a case by case basis and may require a trigger, where you could add a sorting column that you parse the month out.

One of the ways I might handle this is to treat the number series as a string, and Substr() out the month. This could be done in a Where clause:

WHERE SUBSTR([DATE], 3, 2) > "07";

It could also be preformed in a correlated sub-query or with an Order/Group by clause. This method has some big limitations, in that the length of the value can't change; and that less than/greater than comparisons for strings are dependent on your database structure and it's collation.

Another option is to re-serialize the month and year using something like YYYYMM as a pattern to create your filter. For that matter, you can re-pattern the number into YYYYMMDD and be able to sort using YYYYMM00. Personally, I would use this method to keep the date numeric for sorting. (I am assuming ANSI for String Concatenation - SQL Server '+' operator, Oracle is '|', and ANSI/Postgre is '||')

SELECT [SERIALIZED_DATE] 
FROM YEAR(CONVERT(DATETIME,"07/26/2013")) || MONTH(CONVERT(DATETIME,"07/26/2013")) || DAY(CONVERT(DATETIME,"07/26/2013"));  
...
WHERE [SERIALIZED_DATE] > 20130700;

Hi all, heartful thanks for quick response.

Actually data is being sent by 2nd partner of the client & i m just working on application with that data. So data insertion part is not in my hand.

SELECT CONVERT (date,STUFF(STUFF('260713',5,0,'/'),3,0,'/'),3)

is working & thats what i am looking for & i have to use in my SP.

Thnks guys & Thanks PIEBALDconsult