| Author |
Topic |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2011-08-22 : 09:40:36
|
Have following that gets and convert date from a db2/400 system. The date format is a JDEdwards date (example 111182 which is century, year and day of year). Result is 2011-07-01.I would like the date format to be mm/dd/yyyy (07/01/2011). Not sure how to to that within existing line. Thank you.case when shtrdj > 0 then char(date(digits(decimal(shtrdj + 1900000,7,0)))) else '''' '''' end shtrdj, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 13:50:54
|
Doesn't make much sense to me."example 111182"That is year 2011? or year 1111 ?82nd day of the year? I can't figure out how to get that to be the 7th January, or the 1st JulyIf I've understood what a JDEdwards date format is you can convert it to DATETIME with:DECLARE @strJDEdwards varchar(10)SELECT @strJDEdwards = '201182'SELECT [SQL Date] = DATEADD(Day, CONVERT(int, RIGHT(@strJDEdwards, 2))-1, LEFT(@strJDEdwards, 4) + '0101') assuming that 1st January 2011 would be 201101, if 1st January is 201100 then you don't need to subtract 1 - i.e.:DECLARE @strJDEdwards varchar(10)SELECT @strJDEdwards = '201182'SELECT [SQL Date] = DATEADD(Day, CONVERT(int, RIGHT(@strJDEdwards, 2)), LEFT(@strJDEdwards, 4) + '0101') if JDEdwards date is years-after-1900 then I expect you can adjust the above accordlying.Note that DATETIME datatype in SQL is an object with date & time format, it has not specific formatting per se. You should do the formatting in you application, or if that is not possible then use CONVERT(CHAR(nn), MyDateTime, FFF) where "FFF" is a formating directive (see SQL documentation for the various permitted values) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 14:01:01
|
| OK, so its not a 4 digit year, but 3 digit year and 3 digit day ... (now I think about it it needs to be 3 digit day of course) ... and thus I can see that Visakh's solution will split the parts off - assuming you are starting with an integer datatype object - and relying on SQL's parsing of a 4-digit-date as first day of the year (which is better than my concatenation of "0101" to get 1st January) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-22 : 15:05:42
|
| I think the date conversion in SQL is understood, from this forum topic:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129129I guess I'm not sure what the orginal question is. Is it just converting a date to a string in the format MM/DD/YYYY or trying to convert a date to s string in DB2 or what? |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2011-08-22 : 17:08:32
|
| Question is to convert to mm/dd/yyyy format. Current format I get is yyyy-mm-dd.JDE date format is:first position = century, next 2 positions = year, remaining is day of year Thank you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 18:01:13
|
| "Question is to convert to mm/dd/yyyy format"So you want a string, rather than a datetime object?Because "the DATETIME datatype in SQL is an object to hold date & time, it has no specific formatting per se"If you are storing the value in SQL Server, in a date / datetime object, then you do not want a string in "mm/dd/yyyy" format because it is ambiguous (and you could just store the calculated value directly into a datetime object) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-22 : 23:49:26
|
| and as always Madhi points out, you can do formatting of dates in your front end apps as its a presentation issue and need not write sql code for it (unless you dont have any way to handle it in app)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2011-08-24 : 11:05:17
|
| I think I'll take the suggestion of formatting the date in the front end application where I have better leverage. I thank you all for your input. |
|
|
|
|
[Resolved] Set date format