Converting three int parameter to one date

Question

Consider three parameters:

@Y=2014
@M=11
@D=24

I want to have a function in SQL Server which gets three numbers and return one date as result.

CrimsonKing · Accepted Answer · 2014-11-24T12:32:04.370

6

You can use SQL Server 2012 DATEFROMPARTS function.

SELECT DATEFROMPARTS(@year, @month, @day)

For versions below 2012, I'd use:

SELECT CONVERT(DATETIME, STR(@year * 10000 + @month * 100 + @day))

edited Nov 24 '14 at 12:32

answered Nov 24 '14 at 12:22

CrimsonKing

2,696
1
14
11

sqlserver 2008 please – Behnam Nov 24 '14 at 12:26
@Behnam, tag the question appropriately – Jodrell Nov 24 '14 at 12:29

Gordon Linoff · Answer 2 · 2014-11-24T12:46:50.493

1

You could do:

select cast(cast( (@y * 10000 + @m * 100 + @d) as varchar(255)) as date)

But datefromparts() is best if you are using SQL Server 2012+.

edited Nov 24 '14 at 12:46

answered Nov 24 '14 at 12:27

Gordon Linoff

1,242,037
58
646
786

Its not working. `Conversion failed when converting date and/or time from character string`. – Pரதீப் Nov 24 '14 at 12:38
@NoDisplayName . . . I was overly enthusiastic with the `0` key. Here is a SQL Fiddle: http://www.sqlfiddle.com/#!3/d41d8/41140. – Gordon Linoff Nov 24 '14 at 12:47
Good analytic skills – Pரதீப் Nov 24 '14 at 12:59

score 0 · Answer 3 · answered Nov 24 '14 at 12:28

create function dbo.formatDate(@Y int, @M int, @D int)
returns date
as
begin
    declare @retDate date
    select @retDate =  cast(cast(@Y as varchar) + '-' + cast(@M as varchar) + '-' + cast(@D as varchar) as date)
    return @retDate
end

Testing:

select dbo.formatDate(2014, 11, 24)

score 0 · Answer 4 · answered Nov 24 '14 at 12:29

0

SELECT CONCAT(day, '/', month, '/', year) AS Date

answered Nov 24 '14 at 12:29

Matt

14,906
27
99
149

Good because it doesn't work in the United States and, bad because it doesn't work in the United States. etc. – Jodrell Nov 24 '14 at 12:30
it can be moved around to suit the country's date format – Matt Nov 24 '14 at 12:31
Oh, and the result is a `varchar` that looks like a date, not a `DateTime`. – Jodrell Nov 24 '14 at 12:38

score 0 · Answer 5 · edited May 23 '17 at 12:20

0

this is a duplicate of this question

https://stackoverflow.com/a/1923918/3632420

SELECT
   CAST(
      CAST(year AS VARCHAR(4)) +
      RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
      RIGHT('0' + CAST(day AS VARCHAR(2)), 2) 
   AS DATETIME)

edited May 23 '17 at 12:20

Community

1
1

answered Nov 24 '14 at 12:29

sdhd

123
2
10

Rajesh · Answer 6 · 2014-11-24T12:49:37.337

0

If you are using SQL Server 2000 and above use the below query

Select MyDate=cast(DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0))) as date)

If you are using SQL Server 2012 and above use the built function DATEFROMPARTS like

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

edited Nov 24 '14 at 12:49

answered Nov 24 '14 at 12:34

Rajesh

1,600
5
33
59

score 0 · Answer 7 · edited Nov 24 '14 at 12:48

0

Try this,

declare  @Y int=2014
declare  @M  int =11 
declare  @D int =2

SELECT CONVERT(VARCHAR(12), (SELECT Cast(@d AS VARCHAR(20)) + '-'
                                    + Cast(@M AS VARCHAR(20)) + '-'
                                    + Cast(@y AS VARCHAR(20))), 110)

Change the 110 accordingly needed for different formats

formats in sql server datetime conversion

edited Nov 24 '14 at 12:48

Rajesh

1,600
5
33
59

answered Nov 24 '14 at 12:44

Recursive

954
7
12

Converting three int parameter to one date

7 Answers7