incorrect result from Format and DatePart functions combination 1

[zyrag]

I want to get the month part from a Date field in my query so i established my query like,

SELECT DatePart('m', Trans_Date) AS TransMonth FROM tblTransactions

The result will give me TransMonth=4 for April and so on... Further, instead of returning an integer value, i want to get the month name itselt, say "April", so i used the Format function like,

SELECT Format(DatePart('m', Trans_Date), 'mmmm') AS TransMonth FROM tblTransactions

However, the result always returned with the value TransMonth="January" even though Trans_Date='04/30/03' OR Trans_Date='any_other_date'.

How do i fix this problem? If you could give me a better method for the purpose, pls let me know.

Thanks in advance,

 

[zyrag]

got it fixed now...

SELECT Format(Trans_Date, 'mmmm') AS TransMonth FROM tblTransactions


Still would like to hear from you for a better suggestion..

 

[CCLINT]

If you do not need this value in the criteria, why do you want to convert it with-in the sql statement?
Not too scalable...unless this is a stored proceedure.

Format it locally - when you pull in the data. Most bound controls, also the datagrid, have a format property for this purpose, if you are using bound controls.

If this is a stored proceedure (doesn't look like it seeing the DatePart punctuation), then ignore my remarks.

 

[zyrag]

thanks CCLINT... It's not a stored procedure and the SQL statement is used in the Command object in DED for generating a report for the transactions within the specified month. It is also needed in the criteria say,

WHERE Format(Trans_Date, 'mmmm')=? AND Format(Trans_Date, 'yyyy')=?

to pull out only those records with the specified month and year, where by default, year=current_year in the textbox within my form calling the report.

I'm still open for a better method...

Anyway, CCLINT how do i pass/recieve variables in my form into the Command object in DED? I mean, passing/recieving variables not part in my SQL statement of the Command object in DED?

 

[CCLINT]

AAhh. Therefore I stated:
"If you do not need this value in the criteria..."
Could not see this in the orig. question.

If you want the numeric of month or year, then use those functions - YEAR(DateField) - instead of the Format function as this is much more efficient.
And, better is to format the criteria value, and not the field:

"...WHERE Trans_Date BETWEEN ? AND ?"

Parameter1 Value = DateSerial(SomeYear, 1, 1)
Parameter2 Value = DateSerial(SomeYear, 12, 31)

Command object variables: Use a Parameter object with it. Examples (not for the data environment) on how to use Parameters and Command Object available in one of the FAQs (deals with using single quotes in a SQL Statement)

Hope this helps...

 

[zyrag]

Thank you CCLINT... The functions YEAR and MONTH works great. However, I am currently still stuck with my passing/recieving of parameters problem. I have tried using

PARAMETERS ParamName DataType

This command works great but i wasn't able to use it with a SQL statement that uses the TRANSFORM...PIVOT command. Would i be able to use PARAMETERS command along with TRANSFORM...PIVOT? How? I mean, which should come first?

I tend to use the PARAMETERS command coz i can't get a working query using the command in DED say,

TRANSFORM SUM(Debit)
SELECT Branch_Code, Last_Name+ ', ' + First_Name AS EmpName, Reason FROM tblTransDetails WHERE Branch_Code=? GROUP BY...

The point i'm trying to show is that, i dont seem to get a working query using a parameter (WHERE Branch_Code=?) so i experimented using the PARAMETERS like,

PARAMETERS pBranchCode TEXT; TRANSFORM SUM(Debit)...and so on...

but dont seem to get right with it either...

Hope you could help me with this....