SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Date to First day of the month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cwildeman
Starting Member

USA
40 Posts

Posted - 11/16/2011 :  08:44:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/16/2011 :  08:56:56  Show Profile  Reply with Quote
select dateadd(month,datediff(month,0,getdate()),0)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

cwildeman
Starting Member

USA
40 Posts

Posted - 11/16/2011 :  09:03:50  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/16/2011 :  12:47:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/16/2011 :  12:54:08  Show Profile  Reply with Quote

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


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

Go to Top of Page

cwildeman
Starting Member

USA
40 Posts

Posted - 11/16/2011 :  13:08:45  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/16/2011 :  17:57:56  Show Profile  Reply with Quote

SELECT dateadd(month, datediff(month, 0, Consultdate), 0) as ConsultDate2
FROM   ...



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/17/2011 :  00:31:45  Show Profile  Reply with Quote
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/

Go to Top of Page

cwildeman
Starting Member

USA
40 Posts

Posted - 11/17/2011 :  16:48:23  Show Profile  Reply with Quote
Thanks again for everyone's help. These suggestions worked. chuck

Chuck W
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/17/2011 :  18:28:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can make a persisted calculated column too...


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/18/2011 :  01:31:15  Show Profile  Reply with Quote
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/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/21/2011 :  02:55:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

fish462
Starting Member

4 Posts

Posted - 09/18/2013 :  16:00:20  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2013 :  16:07:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, theDateTimeCol), -1)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/18/2013 :  16:11:58  Show Profile  Reply with Quote
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"..

Edited by - Lamprey on 09/18/2013 16:15:15
Go to Top of Page

fish462
Starting Member

4 Posts

Posted - 09/19/2013 :  08:50:15  Show Profile  Reply with Quote
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"
Go to Top of Page

fish462
Starting Member

4 Posts

Posted - 09/19/2013 :  09:03:45  Show Profile  Reply with Quote
Nevermind, I got it. The LAST_DAY function worked.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/19/2013 :  11:07:45  Show Profile  Reply with Quote
FYI, This is a SQL Server forum. So, you'll probably get better help if you post in an Oracle forum.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/22/2013 :  02:32:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000