Set Date based on day of Month

Question

Set Date based on day of Month

SQL_Kills

Hall of Fame

Points: 3154
More actions
July 21, 2016 at 3:33 am

#320045

Set Date based on Month
Hi,
I want to set a date to be always two years behind but one want this changing if it falls on the first of April (01-APR)
It also needs to be in DD-MMM-YYYY format.
declare @date as date
Set @date = '01-APR-2016'
--GETDATE()
Select replace(upper(convert(varchar,@date,106)),' ','-') as NewDate
So this example i want the NewDate to be '01-APR-2014'
Thanks

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Dates have no format when they are stored in the database: the date format is something that helps human beings interpreting the date, but in the database they are stored in a format-agnostic way.

If you need to format a date in a particular way, do that from the client application.

That said, what you need to do to obtain two years' ago today is:

SELECT DATEADD(year, -2, GETDATE())

-- Gianluca Sartori

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

Hi,

Okay thanks, but how do I fixed the date to be '01-APR' and then the relevant year?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

This will return 1st April from the year two years before the given date. If you need the date presented in a particular way, do that in the application. If you must use T-SQL, use the CONVERT function and the applicable style number.SELECT DATEADD(month,12*(DATEDIFF(year,'19000101',GETDATE())-2)+3,'19000101')

John

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

Quick question, if the date is earlier than the 1st of April then should it return the year - 2 or -3?, i.e.

a) 2016-01-01 ==> 2014-04-01 (21 months)

b) 2016-01-01 ==> 2013-04-01 (33 months)

😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

John Mitchell-245523 (7/21/2016)
This will return 1st April from the year two years before the given date. If you need the date presented in a particular way, do that in the application. If you must use T-SQL, use the CONVERT function and the applicable style number.SELECT DATEADD(month,12*(DATEDIFF(year,'19000101',GETDATE())-2)+3,'19000101')
John

Similar logic, shorter version.

SELECT DATEADD(month,12*(YEAR(GETDATE())-1902)+3,0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

And yet another option:

SELECT DATEADD(MM, DATEDIFF(MM, '19020401', GETDATE()), 0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yes! I like that. I think I prefer going with explicit dates rather than integers - it makes it easier to see at a glance what it's doing. But like I said, that's just a personal preference.

SELECT DATEADD(MM, DATEDIFF(MM, '19020401', GETDATE()), '19000101')

John

ben.brugman SSChampion Points: 13369 More actions · Answer 8

spaghettidba (7/21/2016)
Dates have no format when they are stored in the database

Dates definitively have a format when they are stored in the database.

This format partly determines what you can do and what not. And the format differs between SQL-server, Oracle, Excell and a number of other systems.

The format in SQL-server database is not suetable to be presented to humans, so needs to be presented in a more readable format.

For presentation formats Google on 'CAST AND CONVERT'

SQL code example:

declare @Adate datetime = '20140401'

SET LANGUAGE english -- same as us.

select replace(CONVERT(varchar(30), @Adate, 106),' ','-') -- 01-Apr-2014

SET LANGUAGE us_english

select CONVERT(varchar(30), @Adate, 106)

SET LANGUAGE british

select CONVERT(varchar(30), @Adate, 106)

SET LANGUAGE dutch

select CONVERT(varchar(30), @Adate, 106)

Remarks:

106 does not supply the '-', so here a replace is used.

This is regional settings and language dependend.

See CAST and CONVERT for other options than 106.

See the other replies for the calculation options.

See

For detailed information about date time storage and formats.

Ben

Funfact :

February 30 was a real date in Sweden in 1712.

ben.brugman SSChampion Points: 13369 More actions · Answer 9

ben.brugman

SSChampion

Points: 13369

July 21, 2016 at 8:55 am

#1891353

Oops

ben.brugman SSChampion Points: 13369 More actions · Answer 10

Luis Cazares (7/21/2016)
Similar logic, shorter version.
SELECT DATEADD(month,12*(YEAR(GETDATE())-1902)+3,0)

This is a bit 'short' I would guess:

SELECT DATEADD(month,12*(YEAR('20160401')-1902)+3,0) -- Result 2014-04-01 00:00:00.000

SELECT DATEADD(month,12*(YEAR('20160331')-1902)+3,0) -- Oops ! 2014-04-01 00:00:00.000 <--- This is not correct

Ben

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

ben.brugman (7/22/2016)
Luis Cazares (7/21/2016)
Similar logic, shorter version.
SELECT DATEADD(month,12*(YEAR(GETDATE())-1902)+3,0)
This is a bit 'short' I would guess:
SELECT DATEADD(month,12*(YEAR('20160401')-1902)+3,0) -- Result 2014-04-01 00:00:00.000
SELECT DATEADD(month,12*(YEAR('20160331')-1902)+3,0) -- Oops ! 2014-04-01 00:00:00.000 <--- This is not correct
Ben

Please explain why is it not correct? It's working as intended.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 12

Eirikur Eiriksson (7/21/2016)
Quick question, if the date is earlier than the 1st of April then should it return the year - 2 or -3?, i.e.
a) 2016-01-01 ==> 2014-04-01 (21 months)
b) 2016-01-01 ==> 2013-04-01 (33 months)
😎

Any reason why you don't answer this question? Not providing the necessary information does not help in any way.

😎

ben.brugman SSChampion Points: 13369 More actions · Answer 13

Luis Cazares (7/22/2016)
ben.brugman (7/22/2016)
Luis Cazares (7/21/2016)
Similar logic, shorter version.
SELECT DATEADD(month,12*(YEAR(GETDATE())-1902)+3,0)
This is a bit 'short' I would guess:
SELECT DATEADD(month,12*(YEAR('20160401')-1902)+3,0) -- Result 2014-04-01 00:00:00.000
SELECT DATEADD(month,12*(YEAR('20160331')-1902)+3,0) -- Oops ! 2014-04-01 00:00:00.000 <--- This is not correct
Ben
Please explain why is it not correct? It's working as intended.

SQL_Kills (7/21/2016)
I want to set a date to be always two years behind but one want this changing if it falls on the first of April (01-APR)

From this I understood that on the first off April the data should change.

So '20160331' would become 2013-04-01 (Two years and something, but less than 3 years)

And '20160401' would become 2014-04-01 (Two years exactly)

This is what I understood from the TP. But I might be wrong, the question was and is not totally clear.

Ben

Sergiy SSC Guru Points: 110206 More actions · Answer 14

ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the database
Dates definitively have a format when they are stored in the database.

Dates do not have format stored in the database.

Full stop.

End of story.

All date-related data types store the dates as binary strings, no place is reserved for formatting.

_____________
Code for TallyGenerator