How to join numbers in SQL Server

Question

I have some data like this in a table:

year           month        day           ID
2013            1             1            x1
2013            1             2            x2
2013            1             3            x3
2013            1             4            x4
2013            1             5            x5
2013            1             6            x6
...
2016            4             10           x1500

This table is a customer table with customerID and customer apply date, but the apply date is split to year & mont & day , data from 2013/1/1 to 2016/4/10 , I want to get the customerID that apply after 2014/4/5.

Here's the code I tried:

SELECT * 
FROM table
WHERE [Year]+'-'+[Month]+'-'+[Day] > '2014-4-5'

But I got this error back:

[Err] 22018 - [SQL Server]Conversion failed when converting the varchar value '2014-4-5' to data type int.

Who can help me?

Check this, it will be the solution to your issue: http://stackoverflow.com/questions/266924/create-a-date-with-t-sql — David, Apr 13 '16 at 09:28
@David it is not really a good idea to produce a date from the columns. It will is not sargable — t-clausen.dk, Apr 13 '16 at 10:02

score 3 · Answer 1 · edited May 23 '17 at 10:29

This should work and keep your code sargable

SELECT * 
FROM table
WHERE 
  [Year] = 2014 and [Month] = 4 and [Day] > 5
  OR [Year] > 2014
  OR [Year] = 2014 and [Month] > 4

Another useful method would be making a PERSISTED computed column, persisted columns are sargable and you can even put an index on that column:

ALTER TABLE <tablename> ADD actualdate AS 
  CONVERT(date, cast(year as varchar(4))+'-' +
  cast(month as varchar(2)) +'-'+ cast(day as varchar(2)), 126) PERSISTED

Then you can simply type

WHERE actualdate > '2014-04-05'

HoneyBadger · Accepted Answer · 2016-04-13T09:53:35.433

2

Backs' solution is nice and easy (edit: but incorrect, thanks @t-clausen.dk for pointing that out), a different option would be DATEFROMPARTS():

WHERE DATEFROMPARTS([Year], [Month], [Day]) > '2014-04-05'

Edit: DATEFROMPARTS() is only available in sql-server 2012 and up.

edited Apr 13 '16 at 09:53

answered Apr 13 '16 at 09:31

HoneyBadger

14,750
3
34
48

2

@Backs solution is incorrect, it doesnt allow jan 2015 or 2015-01-01 – t-clausen.dk Apr 13 '16 at 09:34
@t-clausen.dk I just tested the sql , it runs ok ,why you say solution is incorrect? Thanks! – Walker.li Apr 13 '16 at 09:54
@Walker.li the query he wrote will not return any following years when month is from jan to april 5. It will not return 1-4 december, november etc 2014 either – t-clausen.dk Apr 13 '16 at 09:57
@t-clausen.dk Thank you ! Though I can't test the error out , but I will remember it . – Walker.li Apr 13 '16 at 10:13

score 0 · Answer 3 · edited May 23 '17 at 12:10

0

This is dependant on language settuings.See this answer for more info..

create table #test
    (
    year int,
    month int,
    date int,
    name char(20)
    )


    insert into #test
    select 2012,1,14,'ab'


    ;with cte
    as
    (
    select *, datefromparts(year,month,date) as datee
     from #test
     )
     select * from cte where date>'somedate'

edited May 23 '17 at 12:10

Community

1
1

answered Apr 13 '16 at 09:38

TheGameiswar

27,855
8
56
94

Ullas · Answer 4 · 2016-04-13T10:26:22.937

0

You have to cast the columns Year, Month and Day to varchar.

Query

SELECT * FROM Customer
WHERE CAST((
  CAST([Year] as varchar(4)) + '-' + 
  CAST([Month] as varchar(2)) + '-' + 
  CAST([Day] as varchar(2)))
) > '2014-04-05';

edited Apr 13 '16 at 10:26

answered Apr 13 '16 at 09:39

Ullas

11,450
4
33
50

Nandhini · Answer 5 · 2016-04-13T10:08:56.200

0

Try this,

SELECT * 
FROM TABLE WHERE
DATEADD(year, [year]-1900, DATEADD(Month, [Month]-1, DATEADD(Day, [Day]-1, 0))) >  CAST('2014-4-5' AS DATETIME)

(OR)

SELECT * 
FROM table
WHERE CAST(CAST([Year]+'-'+[Month]+'-'+[Day] AS VARCHAR(15)) AS DATETIME >  CAST('2014-4-5' AS DATETIME)

edited Apr 13 '16 at 10:08

answered Apr 13 '16 at 09:50

Nandhini

344
2
4

1

[Year]+'-'+[Month]+'-'+[Day] this will return a numeric value and an incorrect result, once you convert it will translate into a date like: 1905-07-18, – t-clausen.dk Apr 13 '16 at 09:55
1

second answer is still incorrect, you need to cast the individual columns – t-clausen.dk Apr 13 '16 at 11:18

How to join numbers in SQL Server

5 Answers5