need SQL Expression to convert date instead of a crystal formula so it can be used in selection

[japeconsulting]

Hi
I have a field in my database which contains the day number of the year which is calculated from january 1st, so for example January 10 would just contain 10 and december 31st would contain 365.

I need to work out from that the day and the month. I can write a formula in crystal syntax to do this (see below), but my problem is that I need to use this date to be used as selection criteria in the report and if I use the crystal formula it doesnt put this criteria in the WHERE clause. If I could write the same formula as a sql expression this would resolve the problem. I have struggled trying to create case statements in sql expressions but cant get it to work.

Any suggestions please??

//current crystal syntax formula
NumberVar v_day := {database field});
NumberVar xm := 12;
if (v_day = 0) then v_day := 1;
if (v_day <= 31) then xm := 1;
if (v_day >= 32 and v_day <= 59) then xm := 2;
if (v_day >= 60 and v_day <= 90) then xm := 3;
if (v_day >= 91 and v_day <= 120) then xm := 4;
if (v_day >= 121 and v_day <= 151) then xm := 5;
if (v_day >= 152 and v_day <= 181) then xm := 6;
if (v_day >= 182 and v_day <= 212) then xm := 7;
if (v_day >= 213 and v_day <= 243) then xm := 8;
if (v_day >= 244 and v_day <= 273) then xm := 9;
if (v_day >= 274 and v_day <= 304) then xm := 10;
if (v_day >= 305 and v_day <= 334) then xm := 11;
if (v_day >= 335 and v_day <= 365) then xm := 12;

if (xm >= 2) then v_day := v_day - 31;
if (xm >= 3) then v_day := v_day - 28;
if (xm >= 4) then v_day := v_day - 31;
if (xm >= 5) then v_day := v_day - 30;
if (xm >= 6) then v_day := v_day - 31;
if (xm >= 7) then v_day := v_day - 30;
if (xm >= 8) then v_day := v_day - 31;
if (xm >= 9) then v_day := v_day - 31;
if (xm >= 10) then v_day := v_day - 30;
if (xm >= 11) then v_day := v_day - 31;
if (xm >= 12) then v_day := v_day - 30;
Date (2013, xm, v_day)


Thanks

 

[briangriffin]

For SQL Server:

DATEADD(Day,{yourDBfield} - 1,DATEADD(year,113,0))

The 113 represents the year you want minus 1900. You'd probably want to modify the formula to accommodate future years without having to modify the report.

 

[PeterDimitrov]

DATEADD(dd,{database day field},'12/31/2012')

You can avoid hardcoding the date by using this formula
DATEADD(dd,{database day field},DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)-1)

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.