Hi,
I am trying to figure out and hope u guys help me.
I need a only 6 digit ID only eg
55 = 000055
405 = 000405
5544 = 005544
How i can do it in SQL server and which formula/datatype i must use?
Deaf can Do anything, except hear
I want to build a string that contains today's date (without the time).
For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either
(1) 2008-04-28
or
(2) 20080428. (I always want the month to be represented by a 2 digit number)
Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.
So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)
The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.
Thanks for looking.
I am currently working on an app and have an issue with a table in the database. The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column. If necessary, I can change the datatype but would prefer not to. The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000. I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?
View 9 Replies View RelatedThere is column mobile_number in my table
I have to replace with star(*) to the middle six character from contact number of 10 digit.
For example
The Contact number is 9334459875
I have to display it as follows
93******75 first two digit and last two only.
Hi
I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.
How do I generate 13 digit UNIQUE random in sql procedure?
Advance thanks
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks
How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.
Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'
Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'
How can I return a 2 digit month into a variable for the months Jan thru September
SELECT DATEPART(mm, GETDATE()) ---> returns 9 for September.. I need 09 returned in order to properly build my target file name...
Thx,
BT
Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem? Thank you
View 3 Replies View Relatedhey all
i am stuck with this little problem
I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'
is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?
thanks
Help, I'm stuck! I have a Customer table:ID Name-- ---------------------1234 Christopher's Surf Boards4321 Christina's NailsI have to build a Account table:ID GPID-- --------------------1234 CHRIST004321 CHRIST01I've built a function that gives me the six alphas and concatenates thetwo digits. The GPID in the account table is the first six alphas ofthe company name plus two digits to ensure uniqueness. (Don't ask why Ican't use the existing id -- the story is long and boring.) I built afunction that creates the alpha part and assigns the first 00. I builta second function that I thought would check the Account table as I wasinserting and increment the numbers if the account number alreadyexisted. It doesn't work. In the above table, both IDs come out asCHRIST00. Here's the second function:ALTER FUNCTION fnValidateID(@mString varchar(15))RETURNS varchar(15)ASBEGINDECLARE@sTemp varchar(15),@sInc varchar(2),@iInc intBEGINset @sTemp = @mStringwhile exists (select GPID from dbo.tempID where GPID = @sTemp)beginset @iInc = convert(int,right(@mString,2)) + 1set @sInc = convert(varchar(2),@iInc)if len(@sInc) < 2 set @sInc = '0' + @sIncset @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sIncend--NEXTENDRETURN @sTempENDMy first guess is that the code is okay, so far as it goes, but that Ican't check the values in the table at the same time I'm inserting intoit. (The Account table is empty, initially.) In any case, I can'tfigure out where I've gone wrong.Thanks.
View 8 Replies View RelatedI'm needing help with the following check digit calculation.
I have a Table with the following columns
Newscan nchar(31)
checkdigit nchar (10)
Newscan ex value 012345670 600888081 307051111
I need to multiply each character by the following sequence starting on the left alternating the multiplyer skipping spaces
21212121
if the multiplyed number is greater than 9 I need to add the two digits together. Ex 2X9 = 18 for a saved value of 9
I then need to add the entire string together
divide that by 10 and take the right most character and subtract that from 10 to end up with a check digit number that I want to store in the column checkdigit.
I have a case statement that converts the alpha chars in the string to the correct numeric values but I'm lost after that on how to accomplish this.
Thanks in advance for any help or direction.
I tried to look at the bank routing check digit function but couldn't get to the code of it.
Bill
I am sure this will be an easy one for you pros, but I am having a case of the Mondays. I am trying to retrieve a date in the format 2007.01.01. In trying to do this I either end up with 1 or null when trying to create the 01 part. Below is some test code for the month portion.
DECLARE @Tst CHAR(4)
DECLARE @tst1 CHAR(2)
SET @tst = DATEPART(Month, '1/1/2008')
SET @tst1 = '00'
--SET @tst = right(@tst1 + @tst, 2)
Select @tst, @tst1, @tst1 + @tst, right(@tst +tst, 2)
Result Set:
1 00 001 [blank]
What am I doing wrong here?
TIA
how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.
View 7 Replies View RelatedI have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.
This is what I have
Code:
SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;
which does what I want it to do except it uses the whole 9 digits.
Any help would be appreciated
I have a field in a table that contains ten digit value representing a datetime. Is there any way to convert it to default datetime formatThanks--Message posted via http://www.sqlmonster.com
View 2 Replies View Relatedcreate function dbo.iso6346_char_to_number(@char char)returns intasbeginreturncase upper(@char)when '0' then 0when '1' then 1when '2' then 2when '3' then 3when '4' then 4when '5' then 5when '6' then 6when '7' then 7when '8' then 8when '9' then 9when 'A' then 10when 'B' then 12when 'C' then 13when 'D' then 14when 'E' then 15when 'F' then 16when 'G' then 17when 'H' then 18when 'I' then 19when 'J' then 20when 'K' then 21when 'L' then 23when 'M' then 24when 'N' then 25when 'O' then 26when 'P' then 27when 'Q' then 28when 'R' then 29when 'S' then 30when 'T' then 31when 'U' then 32when 'V' then 34when 'W' then 35when 'X' then 36when 'Y' then 37when 'Z' then 38endendcreate function dbo.iso6346_check_digit(@string char(10))returns charasbeginreturn (dbo.iso6346_char_to_number(substring(@string,1,1) )*1+dbo.iso6346_char_to_number(substring(@string,2,1) )*2+dbo.iso6346_char_to_number(substring(@string,3,1) )*4+dbo.iso6346_char_to_number(substring(@string,4,1) )*8+dbo.iso6346_char_to_number(substring(@string,5,1) )*16+dbo.iso6346_char_to_number(substring(@string,6,1) )*32+dbo.iso6346_char_to_number(substring(@string,7,1) )*64+dbo.iso6346_char_to_number(substring(@string,8,1) )*128+dbo.iso6346_char_to_number(substring(@string,9,1) )*256+dbo.iso6346_char_to_number(substring(@string,10,1 ))*512)%11end
View 1 Replies View Relatedhi everyone,
I'm looking for statement how to select if length digit = 12. as an example,
data
---------------
123456789012
4567892100
I just want to select if the length of data = 12 digit then the answer is 123456789012
Your kindness really appreciated, I really need the answer as quickly
I am retrieving the current month and setting a variable accordingly.
Code:
DECLARE @currMonthNum INT
SET @currMonthNum = DATEPART(MM, GETDATE())
My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).
Here is my attempt to do this...!
Code:
IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.
I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).
how to display a zero in front of the months that require it?
Using SQL Analyzer or .NET 2.0 programmatically, is there a way to get Transact SQL 2000 to not append a digit at the end of an identifier name when the result set has the same identifier multiple times (from a JOINed SELECT to various tables which have names in common and where I need to get all column names/values in one fell swoop)?
SELECT A.*, B.* from TableA A
INNER JOIN TableB B on (A.Name = B.Name)
The result set will have NAME1 and NAME2, of course (or perhaps it's NAME and NAME1 -- no matter -- I wish to get rid of the numeric suffic).
Thanks much in advance.
I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.
SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')
This works perfect when running in T-SQL, but when attempting to define a variable in the expression
REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?
Anyone run into a simliar issue or have a suggested work around?
I have run into something really annoying.
If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')
But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.
I know there is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049. Is there anything similar within SSIS?
Thanks
Sutha
How to convert 6 digit number to mm/yyyy.
View 5 Replies View RelatedWe are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies View RelatedI have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View 3 Replies View Related1. how to show page number & total page number in report body?
2. how to show total records number?