Get Day from array of days

Question

I am trying to capture a specific day of the week in a long list of dates (TxnDate). The below command I am using is throwing the following error:

Invalid column name 'TxnDay'.

Code

Select DATENAME(dw, ft.[TxnDate]) as TxnDay, ft.[ProductCode], ft.[Site] from dbo.FactTransactions as Ft 
Where ft.[Site] = '1' and TxnDay = 'Tuesday'

EDIT: I have also tried the below with the following error:

Invalid column name 'TxnDay'.

Select ft.[TxnDate], ft.[ProductCode], ft.[Site] from 
dbo.FactTransactions as Ft
Where ft.[Site] = '1' and ft.[TxnDate] in (SELECT DATENAME(dw, ft. 
[TxnDate]) as TxnDay where TxnDay = 'Sunday')

Message is clear. Does the column `TxnDay` exist on table/view `FactTransactions`? Your date parsing isn't the problem here. — EzLo, Jun 13 '19 at 08:45
You are filtering by `TxnDay` on your `WHERE` in both cases, you should repeat the same expression you used on the select to make it `TnxDay` instead. — EzLo, Jun 13 '19 at 08:47
@EzLo Yep - Duplicate indeed! `Select DATENAME(dw, ft.[TxnDate]) as TxnDate, ft.[ProductCode], ft.[Site] from dbo.FactTransactions as Ft Where DATENAME(dw, ft.[TxnDate]) = 'Tuesday'` — Tom Dickson, Jun 13 '19 at 08:52

score 0 · Answer 1 · answered Jun 13 '19 at 09:55

Unless I'm missing something, I'm not sure you need the subquery in your WHERE clause at all - can't you just do:

select 
    ft.[TxnDate], 
    ft.[ProductCode], 
    ft.[Site] 
from dbo.FactTransactions as ft
Where 
    ft.[Site] = '1' 
    and DATENAME(dw, ft.[TxnDate]) = 'Sunday'

score 0 · Answer 2 · answered Jun 13 '19 at 11:35

0

Hi Don't use column's alias name in where clause.Solution is below.

 Select DATENAME(dw, ft.[TxnDate]) as TxnDay, ft.[ProductCode], ft.[Site] from 
  dbo.FactTransactions as Ft 
  Where ft.[Site] = '1' and DATENAME(dw, ft.[TxnDate])= 'Tuesday'

answered Jun 13 '19 at 11:35

Nitika

437
2
10

Get Day from array of days

2 Answers2