select date in form dd/mm/yyyy in sql query

Question

in database table I got :

int day
int month
int year

I want to write a select query to show the date : dd/mm/yyyy

I try this but didn't work :

1-

select (day + '\' + month + '\' + year) as xdate from table1

but give me an error

2-

select (day + '-' + month + '-' + year) as xdate from table1

it return the sum of these field : 2036 instead of 21/1/2014

any solution ?

Can you reproduce the issue using [sqlfiddle](http://sqlfiddle.com/)? — Ryan Gates, Jan 21 '14 at 16:07
Why do you write `select (day + '\' + month + '\' + year) as xdate from table1` if you expect `select (day + '/' + month + '/' + year) as xdate from table1` ? ` \ ` is escape character. — Aycan Yaşıt, Jan 21 '14 at 16:09
Look at this http://stackoverflow.com/questions/266924/create-a-date-with-t-sql — Backs, Jan 21 '14 at 16:09

score 0 · Answer 1 · answered Jan 21 '14 at 16:10

0

If TSQL / SQL-SERVER, then + only works to concatenate strings... So do some conversions on your numbers in that case:

select
   CONVERT(VARCHAR, day) + '\' + CONVERT(VARCHAR, month) + '\' ... etc.

answered Jan 21 '14 at 16:10

Chains

score 0 · Accepted Answer · answered Jan 21 '14 at 16:10

0

Try something like this:

select (cast(day as varchar(2)) + '/' 
    + cast(month as varchar(2)) + '/' 
    + cast(year as varchar(4))) as xdate 
from table1

answered Jan 21 '14 at 16:10

BWS

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

Since you are Casting int to Varchar, so you need to use CAST or CONVERT function

CAST and CONVERT

Try like this

select (Cast(day AS Varchar(10)) + '\' + Cast(month AS Varchar(10)) + '\' + Cast(year AS Varchar(10))) as xdate from table1

(Or)

select (Cast(day AS Varchar(10)) + '-' + Cast(month AS Varchar(10)) + '-' + Cast(year AS Varchar(10))) as xdate from table1

edited Jun 20 '20 at 09:12

Community

answered Jan 21 '14 at 16:10

Vignesh Kumar A

score 0 · Answer 4 · answered Jan 21 '14 at 17:00

0

CONVERT(date, , 103) converts a date to a string representation in dd/mm/yyyy form

SELECT CONVERT(date,DATEFROMPARTS([year],[month],[day]),103)

answered Jan 21 '14 at 17:00

Anon