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

[ijohnson]

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!

[fmvizcaino]

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]

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]

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]

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

Sorry for all the trouble 🙂

[fmvizcaino · Accepted solution]

@ijohnson ,

Lets try this one.

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

[ijohnson]

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