Datepart (to Get 2 Digit Number For The Month)
Apr 28, 2008
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.
View 11 Replies
ADVERTISEMENT
Sep 5, 2001
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
View 2 Replies
View Related
Nov 19, 2007
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
View 6 Replies
View Related
Mar 27, 2007
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?
View 7 Replies
View Related
Dec 16, 2003
I am converting a datetime value to char using datepart(mm,datefield).
I would like the month to be 2 digits - 01,02,03...11,12 - but it only returns 1,2,3...11,12. What can I add to do this? thanks in advance.
View 1 Replies
View Related
Feb 27, 2015
How to convert 6 digit number to mm/yyyy.
View 5 Replies
View Related
Apr 10, 2008
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
View 9 Replies
View Related
Apr 15, 2015
I am writing report to display some IIS site activity, part of the requirement is to produce a trend for user activity for each week of the year.
I have written a stored procedure that uses datepart to split year, month and year week number into separate columns. However the problem I am having is that when a week is split over 2 months I end up with the two entries for the same week but across two months which also splits the count of activity into two rows. So when I produce a line chart in SSRS I end up with a dip due to the week total being split.
An example would be week number 14 of this year is split over two months, I think I need to add same week number activity counts together but not sure how to handle this in the stored procedure.
View 2 Replies
View Related
Oct 15, 2015
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 Related
Sep 12, 2014
There 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.
View 1 Replies
View Related
Aug 22, 2007
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
View 20 Replies
View Related
Aug 22, 2007
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
View 3 Replies
View Related
May 19, 2015
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'
View 9 Replies
View Related
Sep 10, 2014
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
View 5 Replies
View Related
Aug 1, 2007
Hi Guys,
I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-
Wednesday 1st
Thursday 2nd
Friday 3rd
... etc
Can anybody help?
TIA.
View 2 Replies
View Related
Jun 23, 2004
Hi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.
View 6 Replies
View Related
Sep 14, 2012
1. Counts the number of Saturdays in a month
2. Returns 12 rows, each row containing number of Saturdays for each month of the year
3. Single parameter for the specification of the year
View 10 Replies
View Related
Jan 8, 2008
hi need help
i have table employee
i need to change to the employee Once a month the number from 1 to 2 (once a month)
in the first day of the month evry month
and loop back to 1 . 2 .3 .4
evry month next number from 1 to 4
empid val_Number date
-----------------------------------------------------------
11111 1 17/01/2008
2222 4 19/12/2007
3333 3 19/12/2007
i need to see it like next month (in the first day of next the month)
empid val_Number date new_val
--------------------------------------------------------------------------------
11111 1 17/01/2008 2
2222 4 19/12/2007 1
3333 3 19/12/2007 4
TNX
View 7 Replies
View Related
Oct 17, 2006
Good Day to Everyone,
Need help!
I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.
Now my questions are:
1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest).
2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3....
3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?
Thank you so much in advance. :)
View 6 Replies
View Related
Aug 15, 2007
Hi,
Is it possible to get the month name with only an integer representation of the number.
i.e January ,February..... the DATENAME only takes a date as a value.
thanks in advance
View 7 Replies
View Related
Apr 11, 2008
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
As always, your help is very much appreciated!
View 10 Replies
View Related
Mar 28, 2000
I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.
A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.
Thanks in advance
View 1 Replies
View Related
Mar 6, 2015
I am having a problem transforming a data set to the structure I need. I have data in the following format.
Id Visit1 Visit2 Visit3 Visit4
1 2Mar2010 27Mar2010 24Apr2010 8Jul2010
2 2Apr2010 3May2010 4Jun2010 11Jul2010
I need to transform it into a table which gives a count of the number of subjects at each visit at the end of each month.
For example, in March there will be 1 subject at visit 2. In April, there will be a total of 2 subjects, 1 at visit 1 and 1 at visit 3. In May there will be 2 subjects, 1 at visit 3 and 1 at visit 2, etc.
Here is the table I was hoping to produce.
Date.....Subjects.......Visit1.....Visit2.....Visit3.....Visit4
March....1.............................1
April......2...................1.........................1
May.......2.............................1..............1
Jun.......2............................................2
Jul.......2...........................................................2
View 1 Replies
View Related
Sep 4, 2007
Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.
can anyone say the solution?
thanks.
View 2 Replies
View Related
Sep 12, 2006
Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime
Sample data:
Id Name AddDate
1 John 01/15/2005
2 Jane 01/18/2005
.
.
.
101 Jack 01/10/2006
102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
from dbo.sampletable
group by datename(mm, AddDate), datepart(yy, AddDate)
order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
View 3 Replies
View Related
Sep 23, 2015
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
Something like below,
DateTD Days left
2009-01-05 14
2009-01-06 13
2009-01-07 12
2009-01-08 11
2009-01-09 10
2009-01-12 9
2009-01-13 8
2009-01-14 7
2009-01-15 6
2009-01-16 5
2009-01-19 4
2009-01-20 3
2009-01-21 2
2009-01-22 1
2009-01-23 0
2009-02-02 /
2009-02-03 /
View 28 Replies
View Related
Jun 17, 2015
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
View 2 Replies
View Related
Dec 19, 2014
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
View 3 Replies
View Related
Jul 20, 2005
DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth
View 1 Replies
View Related
Aug 26, 2015
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
View 10 Replies
View Related
May 5, 2015
I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?
What i tried
use Cis11101_Northwind
Declare @Julycount int
Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july')
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
View 4 Replies
View Related
May 11, 2007
I know that if I set the Package.MaxConcurrentExecutables Property to -1, it will execute the maximum number of concurrent running executables to equal the number of processors plus two.
I have a requirement where I have to run the maximum number of concurrent running executables. However, the number of concurrent executables to run varies each month (it is not fixed).
If I have a loop inside the package that executes each stored procedure (same sproc, different parameter), will SQL Server execute the maximum number of concurrent running executables possible? (It may execute 4 or 8 at a time and that is fine.)
Thanks,
Michael
View 1 Replies
View Related
Aug 4, 2015
We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
View 6 Replies
View Related