Get first day of first month of previous year in yyyy-mm-dd format

Question

How do I get the first day of the first month of previous year in yyyy-mm-dd format? ie. 2019-01-01. This is the code I have tried:

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Answer 1

You seem to be looking to generate a string, not a date. Consider using date functions and string concatenation: you just need to substract 1 year from the current date, and then append '-01-01'

concat_ws('-', year(getdate()) - 1, '01', '01')

Demo on DB Fiddle

Answer 2

You basically have it. You just need the FORMAT function.

SELECT FORMAT (DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)), 'yyyy-MM-dd') as date

Answer 3

You can use DATEFROMPARTS() function in SQL Server for creating date from given year, month and date in integer as shown below. To get the previous year you can use Year() function and subtract 1 from that. First date and month is always 1 so it has been hard-coded here.

declare @IntYear int = Year(Getdate()) - 1 --Previous Year
Select datefromparts(@Intyear, 1, 1)

The output in SSMS is as shown below.

To get the output in the different format you can follow this link.