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.
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.
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.
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Hi every one, I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview. How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
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
hey 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?
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.
I'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.
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.
I 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.
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
create 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
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?
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
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?
'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,'
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).
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?
Does anyone know how I can get last day of month if I pass a function a given month and and given year. @Month = 2 @Year = 2004 The result I would need is 29 because there are 29 in the month of February in the 2004. Any help on this is greatly appreciated. Kellie
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September: @CurrentDate = 9/10/2007 @EndDate = last working day 9/30/2007
Total# of working days = 15
October: @CurrentDate = 10/1/2007 @EndDate = last working day 10/31/2007
Total# of working days = 23
November: @CurrentDate = 11/1/2007 @EndDate = last working day 11/30/2007
Total# of working days = 22 etc. Any ideas of how i can approch this?
