Aim – To work out what day of the week a Closedate Falls on
Date format is of Closedate is “Year-month-Day” (varchar(50)
E.g.
2013-11-25
2013-11-21
2013-11-19
2013-11-18
Desired results
2013-11-25Monday
2013-11-21Thursday
2013-11-19Tuesday
2013-11-18Monday
How can I set a different language (for example swedish) when using WeekdayName,
DatePart and FormatDateTime?
I try to make the following:
select DATENAME(WEEKDAY,[date]) as DAY, sum(earnings) as Earnings
from myTable
where DATENAME(WEEKDAY,[date]
in ('monday','tuesday','wednesday','thursday','friday')
group by DATENAME(WEEKDAY,[date])
order by DAY
this query works very well except the last line. the last line causes SQL-Server to order the days in an alphabetical order.What do I have to do that SQL-Server orders the days according to their natural appearance (monday, tuesday, wednesday, thurs......)
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007
Any help would be greatly appriciated!
I am trying to calculate age from a date that is short. I have 5.5 million rows and the format looks like this 1945-01-01. I have my query which is:
select mcrmain.dateofbirth,
day(dateofbirth)as d,
month(dateofbirth) as m,
year(dateofbirth)as y, year('1/1/2015') - year(dateofbirth) as age
from anthemq.dbo.MCRMain;
Is the '1/1/2015' the correct way to format if I want a person's age as of 1/1/2015? The output looks like this:
dateofbirthdmyage
1946-04-13134194669
1946-04-13134194669
1946-04-13134194669
1947-03-31313194768
1947-03-31313194768
I want to create a report (report Services) that shows information from start date to end date.
Which function I use to calculate the start date considering that:
end date= today
number of days = -31 days
start day = today - 31 days.
Thank you so much.
IndyW.
Calculating time between two date In Sql Server 2005help me ..
View 3 Replies View RelatedDoes anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies View RelatedI'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)
[code]...
I am looking to calculate the monthly holidays by staff member using DAX. I am able to calculate it if all entries (Start Date & End Date) are confined to a unique month. The issue is where a person's holidays go across several months
I have 2 tables
Table 1 - Holidays
Name StartDate EndDate
Joe Bloggs 27July 2015 7th Aug 2015
Jenny Jones 22nd July 2015 23rd July 2015
Jenny Jones 27th July 2015 28th July 2015
Table 2 -Months&Years
Month/Year
June 2015
July 2015
August 2015
September 2015
I am looking for two types of Output (high level and detailed)
Detailed
Month/Year Name Total Days OOO
July 2015 Joe Bloggs 5
July 2015 Jenny Jones 4
August 2015 Joe Bloggs 5
High level Month/Year Days OOO
June 2015 0
July 2015 9
August 2015 5
I have two tables - one with dates at the end of each month (10/31, 11/30, 12/31, et al) that's linked to a data table containing client signups. While I can get the count per month in a pivot table, I'm trying to calculate the beginning count of each month.
How do I use calculate or something similar to sum the count of records created minus count of records closed prior to the beginning of the period?
The pivot looks like this right now and works by month:
beginning active records ?????
count of records created 100
count of records deleted 50
net records created 50
running balance in net records created 100 (theoretically the next month's beginning balance, but can't figure out how to reference or calculate)
Hi!
Im experiencing problems with datepart(weekday,...) function. In particular with week 54 of 2000... (by the way, dont make a bet about that, because Jan 1st. was saturday (last day of week) and year 2000 was a leap year!!!, so Dec 31 became the one and only case of 54 weeks... in our calendar).
Is there a way I can fix sunday as first day of week, instead using SET DATEFIRST in every procedure? (because "somebody" puts MONDAY as a default in spanish)
Thanx in advance,
Csar.
Hello Guys,
I'm going crazy tring to figure out how to generate a report that execute a Store procedure and then give me a total Per WeekDay and group it by Part Number.
For example my data should look like this
(PartNumber-Monday-Tuesday-Wednesday-Thursday-Friday-Saturday-Sunday-)
The report will give me a list of all available part and the sold sold per part per day.
Thanks in Advance.
Sql server 2000 -sql query analyzerselect datename(dw,'01-01-2006')returns sundayNow I do:set language danishselect datename(dw,'01-01-2006')returns sonday. But it should be sndag. Same goes forsaturday/lordag/lrdagTried substring'ing and ascii'ing/unicode'ing and it is AFAIK an o insteadof the special danish character .Is this a "feature" or am I doing something wrong?I "solved" it by doingselect case DATENAME(dw,'01-01-2006') WHEN 'lordag' THEN 'lrdag' WHEN'sondag' THEN 'sndag' ELSE DATENAME(dw,'01-01-2006') ENDbut it sure aint pretty./jim
View 3 Replies View Related Hi,
I have a transaction file containing a year's worth of transactions with a date/time field that tells me when the transaction took place. I'd like to write a view (or perhaps multiple views) that tells me the average number of transactions per weekday.
I've been messing around with DATEPART and I can calculate the *total* number of transactions that took place on a Monday, for example, but I can't figure how to do the *average* number. My problem seems to be calculating the number of Mondays there were in the year. Or overthinking it.
Any suggestions would be greatly appreciated.
Thanks,
Jennifer
Hi All,In ASP.Net, I am passing a date as a string formatted as yyyymmdd (e.g. 20080219) via an SqlParameter as follows:aSqlParams(0) = New SqlParameter("@DepartDate", inpDepartDate.Value)In the Stored Procedure (SP) I am using DATEPART(dw, @DepartDate).If I execute the SP from within SQL Server and enter a date, formatted as above, as tha value for the Parameter @DepartDate then DatePart returns the correct value (3).If I use the SP via ASP.Net it returns a value which is 1 less (2). When I execute the SP manually the resultant EXEC statement uses "@DepartDate = N'20080219'". Can something similar be done in ASP.Net when setting the SqlParameter? Thank you,
View 4 Replies View RelatedIn a table, I store an integer to represent a day of the week (1 = sunday ) and then in procedures, compare that to datepart(dw, getdate()) to do alternative tasks on certain days.
If I have the number 1, is there a built in function to convert that to sunday or should one just write a function to do just that?
In my SSRS report i have a date parameter and i want to set to it a default value with a complicated logic. I arrived at this strange behaviour:
Today is May 6th, wednesday. If i use the following expression:
DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today())
for the default time picker I get May 9th. If I use exactly the same expression in a textbox in the same report
DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today()).ToLongDateString()
I get May 10th! The only thing that changes is the toString. Why are the two values different? I tried with different expressions and the difference arises when i start using Weekday(Today(), somevalue).
The result of Weekday should be independent from regional settings as I am forcing Sunday to be the first day of the week, right?
I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?
View 3 Replies View RelatedSQL7
I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.
Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.
Using an identity column reduces portability of the table.
Thoughts ?
Craig
Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.
I am looking for something that would return the following output
birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27
If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28
thanks in advance
Zoey
I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?
Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.
Steve
It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...
View 5 Replies View RelatedWhen users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!
View 7 Replies View RelatedI needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
View 2 Replies View RelatedHi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
Skills Table
CatID
SkillID
Skills
SL_ID
1
4
Skill 1
3
2
5
Skill 2
2
2
6
Skill 3
2
3
7
Skill 4
4
3
8
Skill 5
4
4
9
Skill 6
NULL
4
10
Skill 7
NULL
5
11
Skill 8
3
6
12
Skill 9
3
7
13
Skill 10
4
8
14
Skill 11
1
9
15
Skill 12
3
9
16
Skill 13
3
10
17
Skill 14
6
11
18
Skill 15
1
Skills Catagory
CATID
Org
Skillcat
1
Org1
Skill Category 1
2
Org2
Skill Category 2
3
Org3
Skill Category 3
4
Org4
Skill Category 4
5
Org5
Skill Category 5
6
Org6
Skill Category 6
7
Org7
Skill Category 7
8
Org8
Skill Category 8
9
Org9
Skill Category 9
Service Line Table
SL_ID
Service_line
1
Service Line 1
2
Service Line 2
3
Service Line 3
4
Service Line 4
5
Service Line 5
6
Service Line 6
7
Service Line 7
Thanks for all your help in advance.
Dash
Hi,
I'm using SQL Server 7.0 SP3.
I need to calculate the average size of a record in my database to assist in estimating growth.
Is there a quick and/or easy way of doing this?
Thanks in advance,
Darrin
Hi,
I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...
Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'
The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.
Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!
Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST
Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;
ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77
My SQL below gives me
ServerName | ExecTime
-----------------------
server1 | 0.37
Where as i want
ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47
Here is my SQL, hope someone can modify it and thanks in advance.
Code:
SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.
the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.
For Example
If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?
Can someone out there help please
--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:
DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)
SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)
--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.