Convert yyyymm date to mm/yyyy

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);

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?

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

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!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources