Day Of The Week

[Master]

How can I extract the day of the week from a date field?

[MayMusic]

To extract day of the week from a date add this SQL code in a calculated field in a results page or a details page.
 

SELECT DATENAME(dw,[@field:FIELD_NAME])

[Mylene]

Hi, I've tried using  this statement. Unfortunately it doesn't convert time. I receive date as parameter in UK format and I know that data  in the table stores in the US format, so I get error in formula. Is there any workaround?

Thanks

[Mathilda]

Hi Mylene, 

I had the same problem. Try using the following sql query.

SELECT DATENAME(dw, CAST( '[@your_field]' AS DATETIME))

Please replace your_field with the name of your parameter

[scribblemilk]

I've tried the above but i still get an error in formula, i'm trying to take a date (Uk +EU Format) and turn it into a day of the week, if the date is above 12 (16/09/2016) I get an error in formula, below twelve works, so this seems to be an issue with EU to US date format conversion. Normally this would be resolved via changing the setting something like:

SET DATEFORMAT DMY
or
SET LANGUAGE BRITISH

But these don't work either any other ideas?

[Mathilda]

50 minutes ago, scribblemilk said:

I've tried the above but i still get an error in formula, i'm trying to take a date (Uk +EU Format) and turn it into a day of the week, if the date is above 12 (16/09/2016) I get an error in formula, below twelve works, so this seems to be an issue with EU to US date format conversion. Normally this would be resolved via changing the setting something like:

SET DATEFORMAT DMY
or
SET LANGUAGE BRITISH

But these don't work either any other ideas?

Hi scribblemilk,

I see your point. I had the same issue and I resolved it with sql query, which I have provided in my previous reply.

I made a "small" copy of my report. You may check it:

https://c1ect709.caspio.com/dp.asp?AppKey=4325400009a78ab2abf44ee2b5d5

 

Enter 23/09/2016 or 19/09/2016 in the search field.

Can you show your sql query?

[scribblemilk]

18 hours ago, Mathilda said:

Hi scribblemilk,

I see your point. I had the same issue and I resolved it with sql query, which I have provided in my previous reply.

I made a "small" copy of my report. You may check it:

https://c1ect709.caspio.com/dp.asp?AppKey=4325400009a78ab2abf44ee2b5d5

 

Enter 23/09/2016 or 19/09/2016 in the search field.

Can you show your sql query?

My SQL is simply that a one liner:

SELECT DATENAME(dw,'[@Date]')

'Date' is a passed parameter and I simply want to reformat into into a day of the week, I have a localisation of English (UK) to allow for EU format date entry much like your example. Your example seems to work for the dates you suggest but not others (no records) is returned.

 

I've tried the below but I still get a ERROR in FORMULA message, am I missing something?

SELECT DATENAME(dw, CAST('[@Date]' AS DATETIME))