Convert getdate() to CYYMMDD

In SQL 2005 how do I convert getdate() to CYYMMDD.  The only thing I can find is something about AS400 and take convert one of these broken dates back to normal ones.  Any ideas?
NBAISAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Try this - think iSeries dates come out like 01081111, so need to do more like this:
+Convert to YYYYMMDD format.
+Minus 19000000 from the resultant number (i.e. 20081111).
+Convert back to string and concatenate with '00000000' for padding to ensure that 1081111 becomes 01081111 when we take right most 8 characters.
SELECT RIGHT('00000000' + LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)), 8)

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
I believe you want CONVERT() function with code 112 (ISO):
http://msdn.microsoft.com/en-us/library/ms187928.aspx

If my understanding is correct, CYYMMDD is same as YYYYMMDD as C is century, right?

SELECT CONVERT(VARCHAR(8), GETDATE(), 112)
0
 
NBAISAuthor Commented:
CCYYMMDD = YYYYMMDD, but CYYMMDD is apparently different.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
rajeshprasathCommented:
I think you can get your answers here...This article explains step by step procedure to covert SQL date into AS400 date

http://www.scriptsahead.com/sql-server/t-sql/convert-sql-date-into-as400-date-format
0
 
Kevin CrossChief Technology OfficerCommented:
That is exactly what I had except they are only taking 7 characters, so you end up with 1081111.  Just change my code snippet above from:

SELECT RIGHT('00000000' + LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)), 8)

To:

SELECT RIGHT('0000000' + LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)), 7)

It is a flaw to only add one '0' as they have it though if you consider this date which occurs when you use 0 in SQL:
19000101

19000101-19000000 = 101
'0' + 101 = '0101'

This is not too big a deal since you are using getdate here, but programmitically why not be prepared as typing the 7 '0' is not that much harder. :)  Just my humble opinion though.
0
 
Mark WillsTopic AdvisorCommented:
can also do  using the same format numbers, except subtract 100 to get two digit years (ie format 112 becomes 12):

using @d to emulate our datetime column or as a variable...

declare @d datetime
set @d = '19881126'

SELECT convert(varchar,left(year(@d),2) - 19) + convert(VARCHAR(6), @d, 12)

--or

SELECT convert(varchar,left(year(getdate()),2) - 19) + convert(VARCHAR(6), getdate(), 12)
0
 
NBAISAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.