Date format issues with SQL Server as database

<p>Hi All, I've developed reports with dates as parameters and for formatting I'm using MXReportSqlFormat functions.</p><p> </p><p>While selecting from datelookup I give as 2013-09-19, But internally it passes year as 0013.</p><p> </p><p>Could you please shed some light, how to use the dates and formats ? I've tried all the options I know, is there anyway I can replace that 2 zeroes with 20, using replace function ?</p><p> </p><p>Thanks,</p><p>Suresh.</p>

Find more posts tagged with

Comments

smellbop

<p>The way Maximo standard reports do it is by using:</p><pre class="_prettyXprint">MXReportSqlFormat.getEndDayTimestampFunction(params["enddate"])MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"])</pre><p>This *should* convert it to the database format. The start function sets the time to 00:00 and the end function sets it to 23:59.59.999 so you can use it for the between function in SQL.</p><p> </p><p>Alternately, use:</p><pre class="_prettyXprint">string = string.replace(/00/, "20");</pre>

micajblock

<p>I looked at this recently and a date look-up actually returns in mm/dd/yy format. You can perform date calculations to add 2000 years. IMHO this is a bug in Maximo (why would they use a 2 digit year is beyond me).</p>

wwilliams

<div>OK,</div><div>Maybe I am missing something, I have this in a report as smellbop showed</div><div> </div><div><pre class="_prettyXprint _lang-auto _linenums:0">myWhere = " AND ondock.enterdate >= " + MXReportSqlFormat.getStartDayTimestampFunction(params["sDate"])+ " AND ondock.enterdate <= " + MXReportSqlFormat.getEndDayTimestampFunction(params["eDate"])</pre></div><div> </div><div>In the SQL Log for the report I show the full year. Where other than in the application itself are you seeing the two digit year?</div><div> </div><div>ondock.enterdate >= { ts '2012-01-01 00:00:00' } AND ondock.enterdate <= { ts '2012-03-01 23:59:50' }</div>