SQL Server: convert ((int)year,(int)month,(int)day) to Datetime

Question

Possible Duplicate:
Create a date with T-SQL

I've a data table that stores each year, month and day value as ints:

year | month | day
2009 |   1   |  1 
2008 |  12   |  2
2007 |   5   |  5

I need to convert it to datetime value, because I need to use it in a datetime between operation. How could I do this?

This has been asked a few times. See http://stackoverflow.com/questions/266924/create-a-date-with-t-sql — womp, Dec 17 '09 at 19:11

score 71 · Accepted Answer · answered Dec 17 '09 at 19:12

71

In order to be independent of the language and locale settings, you should use the ISO 8601 YYYYMMDD format - this will work on any SQL Server system with any language and regional setting in effect:

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)

answered Dec 17 '09 at 19:12

marc_s

732,580
175
1,330
1,459

thanks, I did it that way too. – balint Dec 17 '09 at 19:21
1

YYYYMMDD works like magic! – UnDiUdin Oct 13 '14 at 13:07
Doesn't work with Firebird 2.5 though, only `YYYY-MM-DD` does – Fr0sT Oct 30 '14 at 12:56
3

@Fr0sT: I **specifically** said this is for **SQL Server** ... – marc_s Oct 30 '14 at 13:21
2

@marc_s calling "MS SQL Server" a "SQL server" is always confusing me. It's like saying "smartphone" meaning "iPhone" only or "OS" meaning "Windows" only. – Fr0sT Oct 30 '14 at 13:27
3

@Fr0sT: **SQL Server** however ***IS*** the official product name! If you want to refer to the class of products, use "database server" or "RDBMS" or something like that. .... – marc_s Oct 30 '14 at 13:28
5

@marc_s yep, Redmond guys sometimes "rock" in naming... – Fr0sT Oct 30 '14 at 13:33
For different locales SQL Server has `DATEFORMAT` setting. See usage here http://stackoverflow.com/a/27355251/1665649 – Konstantin Dec 08 '14 at 10:55
1

You can also use select DATEFROMPARTS(year, month, day) as MyDateValue – Marcelo Lujan May 06 '16 at 03:22
@MarceloLujan: yes - with SQL Server **2012** or newer, you can - but earlier versions didn't have that function – marc_s May 06 '16 at 04:46

gbn · Answer 2 · 2011-03-04T05:51:36.873

38

Pure datetime solution, does not depend on language or DATEFORMAT, no strings

SELECT
    DATEADD(year, [year]-1900, DATEADD(month, [month]-1, DATEADD(day, [day]-1, 0)))
FROM
    dbo.Table

edited Mar 04 '11 at 05:51

answered Dec 17 '09 at 20:08

gbn

422,506
82
585
676

This does require SQL to be configured with 1900 as base date though – Adriaan Davel Dec 05 '17 at 10:27
@AdriaanDavel SQL Server (the question tags) is *always* base date 01 Jan 1900. https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql see "default value". – gbn Dec 05 '17 at 21:39

score 8 · Answer 3 · answered Dec 17 '09 at 19:18

8

You could convert your values into a 'Decimal' datetime and convert it then to a real datetime column:

select cast(rtrim(year *10000+ month *100+ day) as datetime) as Date from DateTable

See here as well for more info.

answered Dec 17 '09 at 19:18

Dominik Fretz

1,368
9
15

This is the best answer – Adriaan Davel Dec 05 '17 at 10:29

score 2 · Answer 4 · answered Dec 17 '09 at 19:09

2

SELECT CAST(CAST(year AS varchar) + '/' + CAST(month AS varchar) + '/' + CAST(day as varchar) AS datetime) AS MyDateTime
FROM table

answered Dec 17 '09 at 19:09

Agent_9191

7,216
5
33
57

4

that's a bad idea - this is dependent on the local settings for your date format. – marc_s Dec 17 '09 at 19:09
1

@marc_s understood, but depending on your deployment needs, it may be a mute point. – Agent_9191 Dec 17 '09 at 19:48
2

naw. If it is a one time ad-hoc query, I could see your point. But not in a stored procedure. marc_s has a good point! – TamusJRoyce Jun 11 '14 at 19:34

SQL Server: convert ((int)year,(int)month,(int)day) to Datetime

4 Answers4

Linked

Related