Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL DATEADD Help Expand / Collapse
Author
Message
Posted Friday, May 17, 2013 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 136, Visits: 579
I have the following:

DECLARE @StartInsertedDate DATETIME

SET @StartInsertedDate = CAST(CONVERT(VARCHAR(10), DATEADD(MONTH, -18,GETDATE()) - 1, 101) AS DATETIME) --DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))

SELECT @StartInsertedDate

AND it returns 2011-11-16 00:00:00.000

I want it to return the 1st day of the month (2011-11-01 00:00:00.000 is what I want) and I can not get it to for the life of me.

Any help will be greatly appreciated.
Post #1454172
Posted Friday, May 17, 2013 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 136, Visits: 579
I give up too easily and over complicate things, I believe I have what I need below:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 18, 0)

Thanks
Post #1454174
Posted Friday, May 17, 2013 1:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 20,859, Visits: 32,880
GBeezy (5/17/2013)
I give up too easily and over complicate things, I believe I have what I need below:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 18, 0)

Thanks


Or this:

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

You may want to check out this:

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1454177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse