Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert yyyymm date to mm/yyyy

Author  Topic 

gravyrice33
Starting Member

3 Posts

Posted - 2014-10-24 : 11:04:22
I have a date called PremPeriod that is an integer.
It is in the format of yyyymm. The day isn't important just the month and year. I need to convert this date to mm/yyyy format.
As of today I haven't found a convert function that only handle month and year. Please help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-24 : 11:43:31
I don't think there is a built-in function in SQL that interprets integers in yyyymm format and converts them to character or date/datetime data types. You could do as in the following example:
DECLARE @x INT = 201409
SELECT RIGHT(@x,2) + '/' + LEFT(@x,4);
Go to Top of Page

gravyrice33
Starting Member

3 Posts

Posted - 2014-10-24 : 12:01:22
quote:
Originally posted by James K

I don't think there is a built-in function in SQL that interprets integers in yyyymm format and converts them to character or date/datetime data types. You could do as in the following example:
DECLARE @x INT = 201409
SELECT RIGHT(@x,2) + '/' + LEFT(@x,4);




Still confused. Why declare an INT as a value (@x int 201409) when the date value resides in table called PermPeriod as described in question? You can't just make up a date. I tried your select statement with the @X replaced with the int in table PERMPERIOD but it gives errors. I have only been using SQL for about a 10 days and I learned on my own so what am I missing?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-24 : 12:32:38
I was only showing an example. Assuming your data is stored in a table called PermPeriodTable, and that the column name PermPeriod, and assuming the data type of the PermPeriod is integer, you would do this:
SELECT
RIGHT(PermPeriod,2) + '/' + LEFT(PermPeriod,4)
FROM
PermPeriodTable
Go to Top of Page

gravyrice33
Starting Member

3 Posts

Posted - 2014-10-24 : 12:36:47
quote:
Originally posted by James K

I was only showing an example. Assuming your data is stored in a table called PermPeriodTable, and that the column name PermPeriod, and assuming the data type of the PermPeriod is integer, you would do this:
SELECT
RIGHT(PermPeriod,2) + '/' + LEFT(PermPeriod,4)
FROM
PermPeriodTable





That works great!
Thank you, Thank you, Thank You!!!
Go to Top of Page
   

- Advertisement -