Convert Date to First day of the month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Date to First day of the month

Author

Topic

cwildeman
Starting Member

40 Posts

Posted - 2011-11-16 : 08:44:51

Hi,
I have a date/time field called ConsultDate. In my query, I want to convert this into the first day of the month it fals in. So if the consult date is 11/14/2011 the converted date would be 11/1/2011.
Thanks,

Chuck W

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-16 : 08:56:56

select dateadd(month,datediff(month,0,getdate()),0)

Jim

Everyday I learn something that somebody else already knew

cwildeman
Starting Member

40 Posts

Posted - 2011-11-16 : 09:03:50

Jim,
Thanks for your help. Just to clarify, I am not trying to convert today's date to the beginning of the month. I have a table of data going back two years with the field consult date. I wanted to change this date to whatever the first day of that particular month was. So if the consult date was 8/19/2010 then the converted date would be 8/1/2010.
Thanks,

Chuck W

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-16 : 12:47:56

You mean you want to overwrite the existing date with the new converted one? If so,
UPDATE <yourTable>
SET Consultdate = dateadd(month,datediff(month,0,Consultdate),0)

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 12:54:08

[code]
UPDATE <yourTable>
SET Consultdate =Consultdate -DAY(Consultdate) +1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwildeman
Starting Member

40 Posts

Posted - 2011-11-16 : 13:08:45

I actually want to keep the ConsultDate data intact and not modify anyting. I want to create a separate field (ConsultDate2)in a Select query which would be the first day of the month for whatever the value of ConsultDate is. So ConsultDate2 would be like a calculated field. Thanks, Chuck

Chuck W

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-16 : 17:57:56

[code]
SELECT dateadd(month, datediff(month, 0, Consultdate), 0) as ConsultDate2
FROM ...
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:31:45

quote:
Originally posted by cwildeman

I actually want to keep the ConsultDate data intact and not modify anyting. I want to create a separate field (ConsultDate2)in a Select query which would be the first day of the month for whatever the value of ConsultDate is. So ConsultDate2 would be like a calculated field. Thanks, Chuck

Chuck W

apply the same logics provided but in a select rather than in update statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwildeman
Starting Member

40 Posts

Posted - 2011-11-17 : 16:48:23

Thanks again for everyone's help. These suggestions worked. chuck

Chuck W

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-17 : 18:28:39

You can make a persisted calculated column too...

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 01:31:15

quote:
Originally posted by cwildeman

Thanks again for everyone's help. These suggestions worked. chuck

Chuck W

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 02:55:08

quote:
Originally posted by visakh16

UPDATE <yourTable>
SET Consultdate =Consultdate -DAY(Consultdate) +1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Note that time part will not be set to starting time with this approach unless time part is not an issue

Madhivanan

Failing to plan is Planning to fail

fish462
Starting Member

4 Posts

Posted - 2013-09-18 : 16:00:20

Is there a way to do the same thing in a select statement with the difference being the query returning the last day of the month?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 16:07:07

SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, theDateTimeCol), -1)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-18 : 16:11:58

You should probalby start a new topic, but here is one way:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101',  <your date here>), '19000131')

EDIT: I read "last day of the next month"..

fish462
Starting Member

4 Posts

Posted - 2013-09-19 : 08:50:15

Thank you both for the responses but neither one of those worked for me. I am getting the following error on the DATEADD and DATEDIFF:
ORA-00904: "DATEDIFF": invalid identifier
00904. 00000 - "%s: invalid identifier"

fish462
Starting Member

4 Posts

Posted - 2013-09-19 : 09:03:45

Nevermind, I got it. The LAST_DAY function worked.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 11:07:45

FYI, This is a SQL Server forum. So, you'll probably get better help if you post in an Oracle forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:32:34

quote:
Originally posted by fish462

Thank you both for the responses but neither one of those worked for me. I am getting the following error on the DATEADD and DATEDIFF:
ORA-00904: "DATEDIFF": invalid identifier
00904. 00000 - "%s: invalid identifier"

thats because the given suggestions are all sql server based and this is indeed a sql server forum. Thats why we suggest to post in relevant forums for specific syntax related help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources