Error with char datatype

Transact-SQL
1

i have declared UID as CHAR(6) and passed data AA00001, then it is successfully executed but the data in the table is
AA0000 what datatype i shud use in oreder to over come this problem an it should throw error that data entered is in valid if i enter more than 6 characters.

2

Nothing in SQL Server allows writing to a char column more characters than the maximum length allows, so it must be some application-side code taking care of the truncation. I would look into the app code first.
Then, if you need a larger column, you just have to specify a higher maximum length (e.g. char(20) instead of (char(6))

3

i have declared UID as CHAR(6) and passed data AA00001, then it is successfully executed but the data in the table is
AA0000 what datatype i shud use in oreder to over come this problem an it should throw error that data entered is in valid if i enter more than 6 characters.

YEAH BUT,

MY REQUIREMENT IS I IF USER BY MISTAKE ENTER AA00001 INSTEAD OF AA0001 . I SHOULD STRICTLY RESTRICT THESE TYPE OF MANUAL MISTAKE ENTRY.

IF THEY ENTER AA00001 INSTEAD OF AA0001 IT MAY CREATE COMPLICATION IN NEXT STAGES OF PROJECT.

4

You can create a check constraint in that column. for example.

ALTER TABLE dbo.myTable WITH NOCHECK 
  ADD CONSTRAINT uid_check CHECK (Len(UID)<=6) ;
5

EVEN THE PROBLEM NOT RESOLVED.

EVEN IF I EXECUTE AS IT SUCCESSFULLY INSERTED EVEN IF I PROVIDE 7 CHARS
exec ALLOC 'ad00044',18050,'prep','01/01/2015'
ad0004 18050 prep 2015-01-01

IT TOOK ONLY FIRST 6 CHARACTERS AND OMITTED THE REST

6

Let them enter a longer value, then adjust it as needed:

DECLARE @UID_ENTERED varchar(30)
SET @UID_ENTERED = 'AA00001'
--SET @UID_ENTERED = 'B000000000999'
--SET @UID_ENTERED = 'ZZ12345'

DECLARE @UID char(6)
SELECT @UID_ENTERED = LEFT(@UID_ENTERED, PATINDEX('%[0-9]%', @UID_ENTERED) - 1) +
SUBSTRING(@UID_ENTERED, PATINDEX('%[1-9]%', @UID_ENTERED), 30)
IF LEN(@UID_ENTERED) > 6
SELECT 'Error!: UID is invalid, more than 6 significant chars entered.'
ELSE
BEGIN
SET @UID = STUFF(@UID_ENTERED, PATINDEX('%[1-9]%', @UID_ENTERED), 0, LEFT('000000', 6 - LEN(@UID_ENTERED)))
END /ELSE/

SELECT @UID_ENTERED, @UID

7

The defined behavior when converting different sized character data is to truncate. From Books Online:
"Converting Character Data
When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated."
Your string value is char(7). When it gets assigned to a char(6) column it implicitly gets converted and therefore truncated. You would need to do some type of data validation before putting it into the database.