cancel
Showing results for 
Search instead for 
Did you mean: 

Converting Crystal Date Group to SQL Command

Former Member
0 Kudos

When selecting the option to group on the server, I found that there are requirements in addition to selecting the option.

I have a date grouping in Crystal like the following:

dateadd("d", - weekday({table.date}, crMonday)+1,{table.date})

Can this be converted to a SQL command?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this


select  Dateadd(d,-(CASE  WHEN DATEName(dw,{table.date here}) = 'Monday' THEN 1
				    WHEN DATEName(dw,{table.date here}) = 'Tuesday' THEN 2	
					WHEN DATEName(dw,{table.date here}) = 'Wednesday' THEN 3
					WHEN DATEName(dw,{table.date here}) = 'Thursday' THEN 4
					WHEN DATEName(dw,{table.date here}) = 'Friday' THEN 5
					WHEN DATEName(dw,{table.date here}) = 'Saturday' THEN 6
					WHEN DATEName(dw,{table.date here}) = 'Sunday' THEN 7
END
)+1, {table.date here})

HTH,

Jyothi

Former Member
0 Kudos

I get an incorrect syntax near the keyword 'select' when I try to save it.

This should be entered into a SQL Expression Field right?

Edited by: Mark Zellner on Sep 4, 2009 7:54 PM

Former Member
0 Kudos

Ya. Please remove select keyword from the query and try.

Jyothi

Former Member
0 Kudos

I think I'm fighting a syntax issue. It doesn't work because I can't get the syntax right.

If I run the stored procedure xp_msver against the MS SQL database table master I get:

ProductVersion 8.00.2282

FileVersion: 2000.080.2282.00

WindowsVersion: 5.2 (3790)

(actually more is returned, but this is what is relevant to the SQL database version info)

The client is using:

Provider: SQLOLEDB

Database Type: OLE DB (ADO)

to query.

Does anyone know the exact syntax? Do I use the double quotes exactly like Crystal Reports inserts them, or do I replace them with single quotes? Do I need to save it with errors?

I've tried a lot of combinations, but they don't seem to help.

Edited by: Mark Zellner on Sep 8, 2009 5:56 PM

Edited by: Mark Zellner on Sep 8, 2009 5:58 PM

Former Member
0 Kudos

Hi Mark,

Jyothi had given an excellent query for you, Congrats Jyothy!!

But she didnt mantioned 'From clouse' on that.

Let me correct that query with an example,

My TableName: Invoice

Primary Key: InvoicePID

Date field:InvoiceDate

Here the query will be

select InvoicePID,  Dateadd(d,-(CASE  WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Monday' THEN 1
				    WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Tuesday' THEN 2	
					WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Wednesday' THEN 3
					WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Thursday' THEN 4
					WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Friday' THEN 5
					WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Saturday' THEN 6
					WHEN DATEName(dw,Invoice.InvoiceDate ) = 'Sunday' THEN 7
END
)+1 ,Invoice.InvoiceDate)
from Invoice

You can change the tablename and field with respect to your DB.

regards,

Salah.

Answers (0)