Concatinating date and string

Question

Concatinating date and string

zi

SSCrazy

Points: 2387
More actions
February 11, 2014 at 11:22 am

#285567

Dear Experts
I trying to insert an int value as a result from concatenation between date and string in sql 2008
insert into customer (custid,fname,sname,serial) values
(6,'Ibrahim','Mohamed' ,(select cast ( (YEAR(getdate())+'0000000')AS int)))
it works but with out the concatenation
any help how to make it works
Thanks lot

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

The problem is that '0000000' is being converted to int and adding it to the year. Instead of converting th char and then to int, I would just use some basic math.

insert into customer (custid,fname,sname,serial) values

(6,'Ibrahim','Mohamed' ,(YEAR(getdate())*10000000))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

zi SSCrazy Points: 2387 More actions · Answer 2

Thanks for replying

but this error raised

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

The statement has been terminated.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Convert it to BIGINT.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Then you'll need to cast as bigint before multiplying.

(CAST( YEAR(getdate()) AS bigint) *10000000)

Why do you need a value like this anyway? What's your serial column data type?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

Luis Cazares (2/11/2014)
The problem is that '0000000' is being converted to int and adding it to the year. Instead of converting th char and then to int, I would just use some basic math.
insert into customer (custid,fname,sname,serial) values
(6,'Ibrahim','Mohamed' ,(YEAR(getdate())*10000000))

Still seems a little weird to me but you can do it like this.

cast(YEAR(getdate()) as char(4)) + REPLICATE('0', 6)

I used REPLICATE instead of '000000'. Makes it easier to see what the value is. 😉

--EDIT--

I meant to copy the OP but got bitten by the quote bug and it had Luis' quote instead.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

zi SSCrazy Points: 2387 More actions · Answer 6

zi

SSCrazy

Points: 2387

February 11, 2014 at 12:02 pm

#1688062

Thank you very much, it works