Concatenate 3 number fields

[rkrause]

I have 3 fields(see below) one for day,month, and year.
p_day p_month p_year

however i try to concatenate them its just adding them up. so with data like this
1 26 2011 i would want to see it like this 1262011. what its doing for me is giving me a number of 2038(adding them)

thanks.

 

[pbaldy]

You'd need to use CAST or CONVERT on each value to force it to be treated as text, like:

CAST(DriverNo As varchar) + CAST(TotalMiles As varchar) AS Test

 

[rkrause]

Ok below gives me what i need, but how could i take the below statement and convert that to a date?


Cast(p_month as varchar) + cast(p_day as varchar) + cast(p_year as varchar) as date

 

[pbaldy]

In Access you'd use DateSerial, but T-SQL has no equivalent. I use one of the MDate functions here. They seem to have improved on it since I last checked:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

That would eliminate the CAST function and you'd just pass your 3 numbers to the function. It can be done without the function, but I find it handier.

 

[rkrause]

Im using Tsl(MSSQL2005), how do i do it without using a function?

 

[pbaldy]

You can use the guts of most of those functions, along with CONVERT, like:

SELECT CONVERT(datetime,dateadd(month,(12*2011)-22801+01,27-1))

2011-01-27 00:00:00.000

Look in BOL at CONVERT for the available formatting options.

 

[rkrause]

im having hard time with the synntax for what im looking for.

This is what i want to convert to a date
Cast(p_month as varchar) + cast(p_day as varchar) + cast(p_year as varchar)

so would i have to use

Convert front, and then something with formatting at the end?

 

[pbaldy]

Try

CONVERT(datetime,dateadd(month,(12*p_year)-22801+p_month,p_day-1))

 

[rkrause]

I know but how do i use that with what im trying to convert? thats where im getting stuck? im not sure how to use what your giving me with my statement?

 

[pbaldy]

Exactly as is, if your field names are correct:

SELECT CONVERT(datetime,dateadd(month,(12*p_year)-22801+p_month,p_day-1)) AS DateField
FROM TableName

Your original question was how to concatenate the number fields into a text field, but now that you want a date you'd want to use the original numeric fields directly.