Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

IN DB, Need to Convert a Date field into the First Day of the Month.

ijohnson
7 - Meteor

I'm doing this in DB.

 

I have a field titled "Event Date", and I need to create a secondary field called "Event Date Month". This new field will be the first day of the month in a YYYY-MM-DD format.

 

Ex. 2019-07-25 needs to be converted into 2019-07-01

 

I'm not very familiar with SQL and I'm having trouble getting that to work.

 

Thanks!

6 REPLIES 6
fmvizcaino
17 - Castor
17 - Castor

Hi @ijohnson ,

 

To calculate that, you can use DATEADD(DAY, 1, EOMONTH([Event Date], -1))

 

Let me know if that works for you.

Best,

Fernando Vizcaino

ijohnson
7 - Meteor

I think I need to change the field type prior to your solution. How would I go about that In DB?

 

Error: Formula In-DB (6): Error: Microsoft OLE DB Provider for SQL Server: Conversion failed when converting date and/or time from character string.\22007 = 241

fmvizcaino
17 - Castor
17 - Castor

Hi @ijohnson ,

 

If your columns is in the yyyy-mm-dd format, you can use the following combination to convert.

 

DATEADD(DAY, 1, EOMONTH(convert(varchar, [Event Date], 23), -1))

 

Best,

Fernando Vizcaino

ijohnson
7 - Meteor

Hi Fernando, the In-DB Formula tool doesn't appear to accept the EOMONTH function. 

 

Sorry for all the trouble 🙂

fmvizcaino
17 - Castor
17 - Castor

@ijohnson ,

 

Lets try this one.

 

DATEADD(month, DATEDIFF(month, 0, convert(varchar, [Event Date], 23)), 0)

 

ijohnson
7 - Meteor

BINGO!  Thanks a lot.  I was going to tear my hair out!

Labels
Top Solution Authors