I have a date that is pulled from a field in the database. It comes out in a dd/MM/yyyy hh:mm:ss format. In an asp.net (but not DNN) environment I previously formatted it with: < asp:Label ID="DateLabel" runat="server" Text='< %# Bind("Date", "{0:D}") %>' /> However I can't quite work out what the syntax should be with SQLView Pro. I checked the wiki (http://wiki.dnnstuff.com/Tokens_Eval.ashx#Formatting_12) and tried to guess what it would be from what is documented there but have not yet worked it out. Can you clarify what the right syntax should be? Thanks in advance. Eoghan

Here's an example from the wiki, down in the formatting section, http://wiki.dnnstuff.com/Tokens_Eval.ashx.

[#EVAL VALUE="StartOfYear(DateTime.Today).ToString("YYYY/MMM/dd")"] - formats as 2010/Jan/01

Basically, it uses the same formatting as the standard .Net formatting as shown here, http://msdn.microsoft.com/en-us/library/az4se3k1.aspx

Richard,
Thanks for the quick reply. The format specifiers in the MSDN page are clear. What is not so clear to me is the syntax for plugging into the example a [DATE] token containing a date retrieved from the Database. Can you enlighten me?!

I haven't tried that but I believe you can do date literals with the # marks. Something like this should work.

[#EVAL VALUE="#[MYDATEFIELD]#.ToString("YYYY/MMM/dd")"]

Here's the literals reference from Flee which I use to do the evals, http://flee.codeplex.com/wikipage?title=LanguageReference&referringTitle=Home#Literals

Hmmm. It didn't work for me. Here's the inner exception stack trace that I am getting. I am wondering if it is the format of the date pulled from the DB:

An error has occurred.
DotNetNuke.Services.Exceptions.ModuleLoadException: DateTimeLiteralElement: String was not recognized as a valid 'DateTime' ---> Ciloci.Flee.ExpressionCompileException: DateTimeLiteralElement: String was not recognized as a valid 'DateTime' at Ciloci.Flee.ExpressionElement.ThrowCompileException(String messageKey, CompileExceptionReason reason, Object[] arguments) at Ciloci.Flee.DateTimeLiteralElement..ctor(String image, ExpressionContext context) at Ciloci.Flee.FleeExpressionAnalyzer.ExitDatetime(Token node) at Ciloci.Flee.ExpressionAnalyzer.Exit(Node node) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.Parser.ExitNode(Node node) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseElement(Production node, ProductionPatternElement elem) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseAlternative(ProductionPatternAlternative alt) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParsePattern(ProductionPattern pattern) at Ciloci.Flee.PerCederberg.Grammatica.Runtime.RecursiveDescentParser.ParseStart() at Ciloci.Flee.PerCederberg.Grammatica.Runtime.Parser.Parse() at Ciloci.Flee.ExpressionContext.DoParse() at Ciloci.Flee.ExpressionContext.Parse(String expression, IServiceProvider services) at Ciloci.Flee.Expression`1.Compile(String expression, ExpressionOptions options) at Ciloci.Flee.Expression`1..ctor(String expression, ExpressionContext context, Boolean isGeneric) at Ciloci.Flee.ExpressionContext.CompileGeneric[TResultType](String expression) at DNNStuff.Utilities.Expression.ExpressionHelpers.EvalulateExpression(String expression) at DNNStuff.Utilities.RegularExpression.DataRowMatch.MatchHandler(Match m) at System.Text.RegularExpressions.RegexReplacement.Replace(MatchEvaluator evaluator, Regex regex, String input, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator) at DNNStuff.Utilities.RegularExpression.DataRowMatch.Replace(String text) at DNNStuff.Utilities.RegularExpression.DataRowMatch.MatchHandler(Match m) at System.Text.RegularExpressions.RegexReplacement.Replace(MatchEvaluator evaluator, Regex regex, String input, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator) at DNNStuff.Utilities.RegularExpression.DataRowMatch.Replace(String text) at DNNStuff.Utilities.RegularExpression.DataSetTokenReplacement.MatchHandler(Match m) at System.Text.RegularExpressions.RegexReplacement.Replace(MatchEvaluator evaluator, Regex regex, String input, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator, Int32 count, Int32 startat) at System.Text.RegularExpressions.Regex.Replace(String input, MatchEvaluator evaluator) at DNNStuff.Utilities.RegularExpression.DataSetTokenReplacement.Replace(String text) at DNNStuff.SQLViewPro.TokenReplacement.ReplaceTokens(String text, Hashtable settings, DataSet ds) at DNNStuff.SQLViewPro.Controls.ReportControlBase.ReplaceTokens(String text, DataSet ds) at DNNStuff.SQLViewPro.StandardReports.TemplateReportControl.BindTemplate() at DNNStuff.SQLViewPro.StandardReports.TemplateReportControl.Page_Load(Object sender, EventArgs e) --- End of inner exception stack trace ---

Do you have just a date or do you have a time value present as well? I think the date literals only work for just the date part. Try doing a convert in your sql statement to include only the date part. I tried the following:

CONVERT(VARCHAR(10),getDate(),103) MyDateField

and it seemed to work ok.

Yes, it does have a time value as well. I will try the covert later on today and let you know how it goes. Thanks.

Hi Richard,

I managed to get it converted to a date format (but with no time component) using a variation on what you had above:

CONVERT(varchar,convert(datetime,MyDateField),103) as MyDateField2

There now remains one nut for me to crack - I need to present the date as '19 April 2010' and not as '19/4/2010'. In ASP.net I managed to do that with:

Text='< %# Bind("Date", "{0:D}") %>'

While the syntax for formattting numbers with SQLView Pro is clear (using, for example, [#EVAL VALUE="String.Format("{0:N0}",[MyNumericField])"]) however the syntax for formatting dates is not clear. Any ideas?!

Thanks, Eoghan