SQL help

Transact-SQL
1

Hi

The code below returns "MM/YYYY" column as text. How can I edit the code to return the column as a format recognized as 'Date' when used in PowerBI?

I know I can change the type to 'Date' as an applied step within Power Query Editor however, this switches the table to import mode and mine needs to be direct.

Thanks in advance

select customer_detail.cd_ow_account As [Account number],
customer_detail.cd_statement_name As [Customer name],
customer_analysis.ca_c_1 As [External Account Manager],
customer_analysis.ca_c_2 As [Internal Account Manager],
customer_analysis.ca_c_5 As [Sales Grading],
customer_analysis.ca_c_4 As [TARGET STATUS],
customer_analysis.ca_l_1 As ECOMMERCE,
customer_analysis.ca_c_7 As [COMPANY SECTOR],
customer_analysis.ca_c_10 As [SUB SECTOR],
customer_profile_target.cpt_year as date,
customer_profile_target.cpt_month,
CONCAT(customer_profile_target.cpt_month, '/', customer_profile_target.cpt_year) as "MM/YYYY",
customer_profile_target.cpt_target As Target,
customer_profile_target.cpt_target_percent As [Target %]
From customer_detail
Inner Join customer_analysis On customer_detail.cd_id =
customer_analysis.ca_customer_id
Inner Join customer_profile_target On customer_detail.cd_id =
customer_profile_target.cpt_cd_id

2

In the absence of DDL and test data it is difficult to tell. The following may return the 1st of the month:

 DATEADD(month, customer_profile_target.cpt_month -1, CAST(customer_profile_target.cpt_year AS date))
3

I get the following error message using the suggested code:

DataSource.Error: Microsoft SQL: Explicit conversion from data type int to date is not allowed.
Details:
DataSourceKind=SQL
DataSourcePath=sqlserver\orderwise;LiveData
Message=Explicit conversion from data type int to date is not allowed.
ErrorCode=-2146232060
Number=529
Class=16

What is DDL? How can I provide this and test data?

4

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

A case in point as I did not know customer_profile_target.cpt_year was an int I assumed it was a string.
As we have no test data we still have to guess. On the basis that an int of 2022 is this year:

CAST(DATEADD(year, customer_profile_target.cpt_year -1900, 0) AS date)
2 Likes
5

You could just use the following: DATEFROMPARTS(customer_profile_target.cpt_year, customer_profile_target.cpt_month, 1)

1 Like