Convert Month Into Its Number Equivalent...(Ex. JAN=1,FEB=2...)

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


ADVERTISEMENT

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

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

How To Convert Pl/sql Code Of Oracle To Something Equivalent Which Works On Sql Server 7.0?

Dec 21, 2000

i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..

with regards,

vijay.

sql server 7.0 on winnt
pl/sql code on oracle 8.0

View 2 Replies View Related

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Aug 15, 2006

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View 4 Replies View Related

Convert Date To Month?

Nov 23, 2011

I am trying to convert the dates from a table to months and year in a sql query

Table name is

Sales

Field is called Sales Date

I want to convert the Sales Date to Month to work out monthly sales

View 2 Replies View Related

Convert Date To First Day Of Month

Nov 16, 2011

I have a date/time field called ConsultDate. In my query, I want to convert this into the first day of the month it falls in. So if the consult date is 11/14/2011 the converted date would be 11/1/2011.

View 18 Replies View Related

How To Get The Day And Day Number For A Specified Month

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

Convert DateTime To Month/Year

Jul 17, 2001

Hi.. can you please supply a SQL statement which will convert a datetime value to a Month Year format?

2001-07-17 14:32:20.763 ---> July 2001

thankyou !
Jen

View 2 Replies View Related

Convert Month Name, Year Range To Mm/dd/yy

Jul 23, 2005

Using SQL 2000. I need to select rows based on a date range. Both thebeginning date and ending date of the range will be entered in themm/dd/yy format. There are 2 columns in the table called MonthOfEvaland YearOfEval, both varchar(9) and both with data like "August" and"2005-2006". I think I need to use these 2 columns to create a date inthe mm/dd/yy format, then use that newly created date to see if it'swithin the date range. How would I go about doing this or is there abetter way?Thanks for any help or advice.

View 1 Replies View Related

Getting The Number Of Hours In A Month

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

Number Of Saturdays In A Month

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

Need To Change Once A Month The Number From 1 - 4

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

Transact SQL :: Convert GL Period To Last Date In Month

Oct 13, 2015

I have a table that records a GL period as a string. I want to convert this value to the last date in the month. 

GlPeriodID
APR 2015

I want to convert to 
4/30/2015

View 7 Replies View Related

Is It Possible To Get The Month Name With Only An Integer Representation Of The Number

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

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 View Related

Count Number Of Pay Days In A Month

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

Help With Dates-finding Number Of Days Per Month

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

Counting Number Of People Accrued Each Month

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

Calculating Number Of Working Days In A Month

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

Get Count And Average Number Of Records Per Month

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

Transact SQL :: Number Of Days Between A Date And Its Month End

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

Transact SQL :: How To Calculate Number Of Employees In Each Month

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

SQL Server 2014 :: Convert 6 Digit Letter To Month And Year

Feb 27, 2015

How to convert 6 digit number to mm/yyyy.

View 5 Replies View Related

CONVERT DATETIME To VARCHAR With Code 106 Does Not Show Long Month

Oct 26, 2007

I am having a problem while converting datetime to varchar with code 106. Here is the code and result I get:

TSQL Code:
SELECT CONVERT(VARCHAR(100), GETDATE(), 106)

Result:
"26 10 2007"

My expected result:
"26 Oct 2007" or "26 October 2007"

Have you encountered this problem before? Is it related to the SQL server setting? Please help and thank you in advance.

View 6 Replies View Related

SQL Server 2014 :: Order By Month Number In A Group By

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

Simple Way To Determine The Number Of Days In A Month For Any Given Date

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

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

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

Script File - Get Total Number Of Orders In Month (July)

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

Transact SQL :: Convert Today Date To MMDDCCYY To A Decimal And Retain 0 In Front Of Month

Aug 24, 2015

This seems to get the job done...

SELECTRIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8)

However, when I try to create it and CONVERT it to a DECIMAL, it then loses the "0"

SELECTCONVERT(DECIMAL(8,0),RIGHT('00000000'+CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8))

Is it impossible to convert it to a decimal and retain the leading "0" on the month?

My vendor's spec states..."Business Date Numeric For 8 positions MMDDYYYY"

View 5 Replies View Related

Execute Maximum Number (that Varies Each Month) Of Concurrent Running Executables

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

Power Pivot :: Divide Number Of Records By End Date Of Month Using DAX Query

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

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

View 9 Replies View Related

How To Convert A Number To String In SQL?

Jul 7, 2006

I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345
Thanks for your help.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved