IN DB, Need to Convert a Date field into the First Day of the Month.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- In Database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Fernando, the In-DB Formula tool doesn't appear to accept the EOMONTH function.
Sorry for all the trouble 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Lets try this one.
DATEADD(month, DATEDIFF(month, 0, convert(varchar, [Event Date], 23)), 0)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
BINGO! Thanks a lot. I was going to tear my hair out!
![](/skins/images/12A9B4B958288E867BE947DD48612FB8/responsive_peak/images/icon_anonymous_message.png)