Create a Date from Day Month and Year With T-Sql

How to create a Date in SQL Server given the Day, Month and Year as Integers

In SQL Server 2012+, you can use datefromparts():

select datefromparts(@year, @month, @day)

In earlier versions, you can cast a string. Here is one method:

select cast(cast(@year*10000 + @month*100 + @day as varchar(255)) as date)

Create a date from day month and year with T-SQL

Assuming y, m, d are all int, how about:

CAST(CAST(y AS varchar) + '-' + CAST(m AS varchar) + '-' + CAST(d AS varchar) AS DATETIME)

Please see my other answer for SQL Server 2012 and above

Create Date From Month and Year in SQL Server 2008

I would use an inline table valued function instead of a scalar function. They are more flexible and better for performance. Also, date do NOT have a format. The format is used in the front end. The ANSI approved format for a date representation is YYYYMMDD. That will work no matter what your local settings are.

Here is a fully functional example of doing this with an inline table valued function.

create function GetDateFromMonthYear
(
    @month int
    , @year int
    , @isStartDate bit
) returns table as return

select MyDate = case @isStartDate 
when 1 
    then dateadd(month, datediff(month, 0, CONVERT(char(4), @year) + right('0' + CONVERT(varchar(2), @month), 2) + '01'), 0)
    else dateadd(day, -1, dateadd(month, datediff(month, 0, CONVERT(char(4), @year) + right('0' + CONVERT(varchar(2), @month), 2) + '01') + 1, 0)) 
end

GO

declare @SomeDates table
(
    MyYear int
    , MyMonth int
    , IsStartDate bit
)

insert @SomeDates
(
    MyYear
    , MyMonth
    , IsStartDate
) values
(2017, 11, 1)
,(2017, 11, 0)
,(2017, 12, 1)
,(2017, 12, 0)

select *
from @SomeDates s
cross apply dbo.GetDateFromMonthYear(s.MyMonth, s.MyYear, s.isStartDate) x

Convert month and year combination to date in SQL

As Marc B said, End month will be hard since you need to figure out what the last day of the specified year/month is.

Try this

DECLARE @StartMonth INT = 3 
DECLARE @StartYear INT = 2010 
DECLARE @EndYear INT = 2013 
DECLARE @EndMonth INT = 6 

SELECT Dateadd(year, @StartYear - 2000,Dateadd(month, @StartMonth - 1, '20000101')) AS StartDate, 
       Dateadd(year, @EndYear - 2000, Dateadd(month, @EndMonth - 1, '20000101')) AS EndDate

How to turn separate year, month and day columns into a single date?

For SQL Server 2008+:

SELECT CONVERT(DATE,CAST([Year] AS VARCHAR(4))+'-'+
                    CAST([Month] AS VARCHAR(2))+'-'+
                    CAST([Day] AS VARCHAR(2)))

For SQL Server 2005:

SELECT CONVERT(DATETIME,CAST([Year] AS VARCHAR(4))+
                        RIGHT('00'+CAST([Month] AS VARCHAR(2)),2)+
                        RIGHT('00'+CAST([Day] AS VARCHAR(2)),2))

Getting only Month and Year from SQL DATE

As well as the suggestions given already, there is one other possiblity I can infer from your question:

- You still want the result to be a date

- But you want to 'discard' the Days, Hours, etc

- Leaving a year/month only date field

SELECT
   DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
   <your_table>

This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.

NOTE: In SQL Server 2008, You will still have the TIME attached as 00:00:00.000
This is not exactly the same as "removing" any notation of day and time altogether.
Also the DAY set to the first. e.g. 2009-10-01 00:00:00.000

Separate date into 3 var (day, month, year) manually with string manipulation

Use conversion function CONVERT() to convert the date from character string value and use DATEPART() function to get specific Day, Month and Year from the date.

DECLARE @string varchar(20) = '15/12/2018'
DECLARE @actualdate date

SELECT @actualdate = CONVERT(date, @string, 103)

SELECT @actualdate AS DATE

SELECT 
       DATEPART(day, CONVERT(date, @actualdate, 103)) Day, 
       DATEPART(MONTH, CONVERT(date, @actualdate, 103)) Month, 
       DATEPART(YEAR, CONVERT(date, @actualdate, 103)) Year

Correct way to convert Month Name and Year to complete date SQL Server

I think it's as simple as this:

SELECT CAST ('January 2016' AS DATETIME)

Or for SQL2012+

SELECT TRY_CAST ('January 2016' AS DATETIME)

Pass day/month/year interval via variable to built-in DATEADD function in T-SQL

With a special tip of the hat to Jonathan Roberts of on SQLServerCentral.com, this should do it for you...

 CREATE OR ALTER FUNCTION dbo.DateRange 
/**********************************************************************************************************************
 Purpose:
 Given a start date, an end date, a "date part", and an increment, return a sequence of rows for the given date part
 according to the dates and the increment.
-----------------------------------------------------------------------------------------------------------------------
 Parameters:

 @StartDate: Start date of the series    - Required - May be greater than @EndDate 
 @EndDate  : End date of the series      - Required - May be less than @StartDate  
 @DatePart : The time unit for @interval - Optional (Default = 'dd')
     ns    : nanoseconds 
     mcs   : microseconds 
     ms    : milliseconds 
     ss    : seconds
     mi    : minutes
     hh    : hours
     dd    : days
     ww    : weeks
     mm    : months
     qq    : quarters
     yy    : years
 @Interval : The number of dateparts between each value returned - Optional (Default = 1)
------------
     Return: DT as a DATETIME2(7) Column of dates and times
-----------------------------------------------------------------------------------------------------------------------
 Sample Calls:
--===== Return a row for every other second in the date range.
 SELECT * FROM dbo.DateRange('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
;
--===== Return a coumt of rows for every millisecond in the date range (Default increment = 1)
     -- A simple DATEDIFF would do the trick but it does demonstate the scope of the function.
 SELECT COUNT(*) FROM dbo.DateRange('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
;
--===== Return a row for each date in the date range (1st default is "dd" and second default is 1)
 SELECT * FROM dbo.DateRange('2011-01-01', '2012-02-03', default, default)
;
--===== Since @StartDate > @EndDate, this returns a row for every 7 days counting backwards.
 SELECT * FROM dbo.DateRange('2012-02-03', '2011-01-01', 'dd', 7)
;
--===== This demonstrates how you can do calculations in the SELECT list.
 SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000',Value),Value,* 
   FROM dbo.DateRange('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
;
--===== See the following link for an example that seriously simplified the task at hand as well as making it easy to 
     -- add other "granularities" (date parts).
        https://www.sqlservercentral.com/forums/topic/tsql-create-dynamic-partition-datarange#post-3944333
-----------------------------------------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 19 Aug 2019 - Jonathan Roberts
        - Initial Release
        - https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

 Rev 01 - 25 Oct 2021 - Jeff Moden.
        - Apply personal code standards and additional information in the documentation
        - Add link to extreme sample usage where the function greatly simplified the task at hand as well as making it
          easy to add other "granularities".
        - Add WITH SCHEMABINDING.
        - Code reduction by moving /@Interval from each THEN to END of CASE.
 *********************************************************************************************************************/  
--===== Function I/O
        (
         @StartDate DATETIME2
        ,@EndDate   DATETIME2
        ,@DatePart  VARCHAR(3) = 'dd'
        ,@Interval  INT        = 1
        )
RETURNS TABLE WITH SCHEMABINDING
     AS RETURN WITH
--===== 16 digit base for InLine Tally Table
H(Z) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))H0(Z)),
--===== InLine Tally Table returns rows starting at 0 based on the DATEPART divided by the interval.
T(N) AS (SELECT TOP(ABS(
                CASE @DatePart
                WHEN 'ns'  THEN DATEDIFF(ns, @EndDate,@StartDate)
                WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate,@StartDate)
                WHEN 'ms'  THEN DATEDIFF(ms, @EndDate,@StartDate)
                WHEN 'ss'  THEN DATEDIFF(ss, @EndDate,@StartDate)
                WHEN 'mi'  THEN DATEDIFF(mi, @EndDate,@StartDate)
                WHEN 'hh'  THEN DATEDIFF(hh, @EndDate,@StartDate)
                WHEN 'dd'  THEN DATEDIFF(dd, @EndDate,@StartDate)
                WHEN 'ww'  THEN DATEDIFF(ww, @EndDate,@StartDate)
                WHEN 'mm'  THEN DATEDIFF(mm, @EndDate,@StartDate)
                WHEN 'qq'  THEN DATEDIFF(qq, @EndDate,@StartDate)
                WHEN 'yy'  THEN DATEDIFF(yy, @EndDate,@StartDate)
                ELSE DATEDIFF(dd --Ensures we get a correct positive value if dates are reversed
                             ,IIF(@StartDate < @EndDate, @StartDate, @EndDate)
                             ,IIF(@StartDate < @EndDate, @EndDate, @StartDate)
                             )
                END/@Interval) + 1) --End of TOP(ABS, Adds 1 interval to makeup for subtraction of INTs
            N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 --So we start at ZERO
           FROM H a,H b,H c,H d,H e,H f,H g,H h -- A maximum of 16^8 (or 2^32) rows can be returned
        )
         SELECT DT = 
                CASE @DatePart            
                WHEN 'ns'  THEN DATEADD(ns, c.CountAmount,@StartDate)
                WHEN 'mcs' THEN DATEADD(mcs,c.CountAmount,@StartDate)
                WHEN 'ms'  THEN DATEADD(ms, c.CountAmount,@StartDate)
                WHEN 'ss'  THEN DATEADD(ss, c.CountAmount,@StartDate)
                WHEN 'mi'  THEN DATEADD(mi, c.CountAmount,@StartDate)
                WHEN 'hh'  THEN DATEADD(hh, c.CountAmount,@StartDate)
                WHEN 'dd'  THEN DATEADD(dd, c.CountAmount,@StartDate)
                WHEN 'ww'  THEN DATEADD(ww, c.CountAmount,@StartDate)
                WHEN 'mm'  THEN DATEADD(mm, c.CountAmount,@StartDate)
                WHEN 'qq'  THEN DATEADD(qq, c.CountAmount,@StartDate)
                WHEN 'yy'  THEN DATEADD(yy, c.CountAmount,@StartDate)
                ELSE            DATEADD(dd, c.CountAmount,@StartDate)
                END 
           FROM T t
          CROSS APPLY(VALUES(IIF(@StartDate<@EndDate
                                ,@Interval*(t.N)  --Count Up
                                ,@Interval*(-t.N) --Count Down
                                )))c(CountAmount)
;

---

Related Topics