[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Format a date for 1st day of last month.

Posted on 2011-05-06
Medium Priority
Last Modified: 2012-05-11
I am trying to capture the 1st day of the previous month,  I need the date in the following format so that I can compare it to another date  2011-01-21 00:00:00.000

The code I have that gives me the date of the 1st day of the previous month is (DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,getdate()))) but this has the time component and i need that set to 000...
Question by:ryanwh
LVL 32

Accepted Solution

Ephraim Wangoya earned 1000 total points
ID: 35705901

select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)

Assisted Solution

dba2dba earned 1000 total points
ID: 35705909
select CAST(FLOOR( CAST( (DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,getdate())))  AS FLOAT ) )AS DATETIME)
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35705959
select DateAdd(month,-1,Convert(datetime, convert (varchar,Year(GETDATE())) + '-'+ convert (varchar,Month(GETDATE())) + '-01'))

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question