Arbitrary Dates
Mar 30, 2006
How can I select all the "filler" dates.
CREATE TABLE tbDateList
( ItemDate datetime )
INSERT INTO tbDateList (ItemDate)
SELECT '01/01/2006' UNION ALL
SELECT '01/10/2006'
SELECT * FROM tbDateList
DROP TABLE tbDateList
In the select statement, I want to select all dates in tbDateList, but I also want to include dates that are not in the list.
Eg: the above select * will return the 2 dates, but I want it to return the 10 dates, 01/01/2006 - '01/10/2006'
Any suggestions?
Mike B
View 4 Replies
ADVERTISEMENT
May 1, 2006
Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.
What I've tried:
DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'
SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1
Result:
Invalid object name '@DBName.dbo.UPR00100'.
I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.
Thanks for the help
Wes
View 2 Replies
View Related
Mar 22, 2007
I have two tables:
Headers(id int, time datetime)
Data(id int, product_id int, property_id int, value float)
Data.id references Headers.id
Headers.id is a primary key,
Data has clustered index (id, product_id, property_id)
Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.
Right now I am doing
SELECT id, time, value
FROM Headers H, Data D
WHERE
H.id = D.id AND
H.time >= @StartTime AND
H.time <= @EndTime AND
D.product_id = @ProductID AND
d.property_id = @PropertyID
This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance
View 1 Replies
View Related
Sep 20, 2006
Suppose the following:
As part of a product install (using InstallShield)...
I create a SQL Server Express instance (say "X") via a silent install
I supply a script to create a database in instance X.
The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:
- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL ServerMSSQL.1, Microsoft SQL ServerMSSQL.2, etc).
Is there a syntactic variant that allows me to avoid this problem?
Thanks
Josh
View 4 Replies
View Related
Apr 11, 2003
CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END
CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END
View 4 Replies
View Related
Jun 22, 2015
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50
View 8 Replies
View Related
Mar 2, 2006
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
View 3 Replies
View Related
Jul 18, 2014
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
View 9 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related
Jun 21, 2005
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies
View Related
Nov 8, 2006
Hi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
View 1 Replies
View Related
Mar 4, 2008
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
View 13 Replies
View Related
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
View 13 Replies
View Related
Mar 30, 2007
Hello All,
I am trying to Add certain number of days to a particular date.
and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver.
Please can anyone help me in achieving it.
thanks
Shiva Kumar
View 2 Replies
View Related
Jan 10, 2002
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters.
I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
Thanks
View 1 Replies
View Related
Apr 19, 2004
OK, so I can return (& therefore use) the last day of last month using
select (GETDATE()- DATEPART ( dd , GETDATE()))
Which returns 31-03-2004 (dd-mm-yyyy)
Has anyone a good way of determining a way of deriving say a date such as 01-01-2004 (1st Jan)....
I'd like to get some data where my dmd.sdate falls always in the previous quarter.. but would like it to be not hard coded...
Currently I have
SELECT cust.no,
cust.surname,
dmd.csan,
dmd.sdate
FROM pdunity.dbo.cust cust,
pdunity.dbo.dmd dmd
WHERE cust.rowno = dmd.rowno_custdmd_cust
AND(dmd.sdate
BETWEEN '01/01/2004'
AND(GETDATE()- DATEPART ( dd , GETDATE())))
ORDER by dmd.sdate asc
Is there some form of function I can use to return 01/01/2004 automatically ?
Will
View 6 Replies
View Related
Nov 21, 2005
why does this sql return the dates in different formats? Is it a default setting on the server or do i just handle it on SQL?
use northwind
select top 20 orderdate from orders
select top 20 left(orderdate,10) from orders
thanks
fatherjack
View 1 Replies
View Related
Feb 11, 1999
We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?
View 2 Replies
View Related
Aug 16, 2002
Hi All
I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.
The startdate is always less than / equal to enddate.
I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.
Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.
I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out
Yusuf
View 1 Replies
View Related
Apr 11, 2008
How would I pull just a list of dates for say the next year or two
View 1 Replies
View Related
Apr 18, 2008
I have an assignment using SQL this is the first time i have used it but i can understand the basics.
It is for an instrument service company and i need to find what instruments have not been serviced for a year.
i already know i have to:
SELECT instrument number, date of last service
FROM Instrument table
but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?
View 4 Replies
View Related
Apr 26, 2008
Hi friends,
I need to display the dates in between two dates without using tables. two dates are today date and 10th date from today date.
View 17 Replies
View Related
Apr 30, 2008
I have a columns with storeID's and dates i need to be able to find the difference of date for a certain store for its Min(Date) to the End of that Month
example
Date StoreID Difference of Date to end of the
Month
2008-01-02 00:00:00.000 163
2008-01-03 00:00:00.000 165
2008-01-01 00:00:00.000 167
2008-01-02 00:00:00.000 180
View 2 Replies
View Related
May 7, 2008
Hi,
I am trying to calculate our job value and product costs over a given time period. But if I run my query, the numbers just dont look right at all. Please can you have a look at this query and see if anything stands out?
-- FINISHED PRODUCTS PRODUCTION REPORTS DATA
-- gets current WIP Job information.
-- requires item details from this.
USE Staging
-- declare start and end variables
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET DATEFORMAT DMY
SET @Start = '01/01/2008'
SET @End = '01/02/2008'
SELECT
SUM(jbs.Value) AS ProducionValue,
SUM(prd.Cost) AS Cost,
CONVERT(CHAR(10), @Start, 103) AS [From],
CONVERT(CHAR(10), @End, 103) AS [End]
FROM
(
SELECT
Job.[Job No],
Job.[Cust Name],
Job.[Req'd Date],
Job.ProdStartActual,
Job.Dept,
[Job].[Value],
Job.Loan
FROM
Job
WHERE
Job.[ProdStartActual] BETWEEN @start AND @End AND
Job.Cancel = 0 AND
Job.Sales = 1 AND
Job.Invoiced = 1 OR
Job.[ProdStartActual] BETWEEN @start AND @End AND
Job.Cancel = 0 AND
Job.Sales = 1 AND
Job.Invoiced = 2
) AS jbs INNER JOIN
(
SELECT
Item.[Job No],
Item.[Code]
FROM
Item
) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN
(
SELECT
Product.[Product Code],
Product.[Cost]
FROM
Product
) AS prd ON itm.Code = prd.[Product Code]
"Impossible is Nothing"
View 5 Replies
View Related
Jun 9, 2008
If I give the from date and to date i should get all dates the between from and to.
eg:
from:'01-June-2008'
To:'03-june-2008'
Result:
Date
'01-June-2008'
'02-June-2008'
'03-June-2008'
Thanks in advance..,
cool...,
View 9 Replies
View Related
Jul 23, 2005
I've got a table (TYPE, DAYMTH) where it is a compound key and theDAYMTH is a datetime but all years are 1900 ONLY THE day and month aresignificant.What I want to do is for each type, find the next n dates - so forexample:TYPE DAYMTH1 1900-02-201 1900-05-051 1900-09-141 1900-11-112 1900-03-032 1900-07-07if I want to get the next 3 dates after 22 June 2005 it should give me:1 2005-09-141 2005-11-111 2006-02-202 2005-07-072 2006-03-032 2006-07-07so, anyone got any bright ideas?I've started down this route:create table #year (yr int)insert into #year values(2005)insert into #year values(2006)insert into #year values(2007)insert into #year values(2008)select *,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)from DAYMTHtable,#yearwhere convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20) > getdate()order by type,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)and although this seems to be a good start I wonder if there are bettersolutions. Also any quick ideas on how to finish if I decide to followmy existing thought process?thanksPhil
View 3 Replies
View Related
Sep 13, 2006
For some reason guys my SQL string isn't working and I'm probably doingsomething stupid?SELECT *FROM sol_sessionSELECT *FROM SOL_SessionWHERE DateGiven '10/09/2006' AND <'13/09/2006'The 'DateGiven' field is a DateTime field, and I can't see quite whatswrong??I simply get a Syntax error!Cheers, Ash
View 15 Replies
View Related
Jul 20, 2005
To support an application, I am integrating it with with Sqlserver2000. The task is simple: get the data at regular intervals, andwrite it into a table dedicated for that application. One of thefields I receive is a date, and it is formatted as an ISO date. Asample value looks like this: "2004-07-09-15.37.52.077258".SQLServer doesn't support this date format, though. The closest Ihave found is the style code "21" or "121" which accepts input as"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()and convert() calls but no solution yet.Is there a way to transform the data to conform to this style acceptedby Sqlserver? I would like to do this in the INSERT statement itselfand not have to code application logic (i.e., user-defined function,etc.) for it.
View 2 Replies
View Related
Jul 20, 2005
HiI would like to create a SP where it will populate TableA based from TableB.TableB will be populated on a monthly basis using a DTS and within that Iwould like to run the SP to populate TableA.Can someone here please help me create the sql statements as a startingpoint.TIA!BobTableB (source)from_date to_date curr_code ex_rate1/1/2004 1/10/2004 CAD .750001/11/2004 1/16/2004 CAD .743211/17/2004 2/4/2004 CAD .722222/5/2004 2/20/2004 CAD .711112/21/2004 2/28/2004 CAD .778883/1/2004 3/3/2004 CAD .790023/4/2004 3/14/2004 CAD .768033/15/2004 3/23/2004 CAD .700223/24/2004 4/2/2004 CAD .73365etc...TableA (destination):date curr_code ex_rate1/2004 CAD 0.738477 calculation:(.75000+..74321+.72222) / 32/2004 CAD 0.737403(.72222+.71111+.77888) / 33/2004 CAD 0.74798(.79002+.76803+.70022+.73365) / 4etc..
View 3 Replies
View Related
Jul 27, 2006
Hi. I hope this is the write place to post this, I hope it is.
basically, I want to be able to return me a query of giving me available dates in a table.
What I mean is that, if I have a table with fields including start date and end date, I want to return me the dates which are NOT listed/stored in the table.
kind of like a booking system, where you store the room reservation dates and obviously you don't want to double book that room, so I would like all the available dates to be returned back.
is this possible? how can I go about doing this?
Thanks!
View 8 Replies
View Related
Mar 12, 2008
Hello people! I have a question: I have a datetime field in my database. When I add a datetime variable (in 12.03.2008 format) to stored procedure (from C# code) that actually updates that field - everything ok. but when I am trying to update a datetime field with string 12.03.2008 (no matter nvarchar or datetime type) in T-SQL code - the date that i receive - 2008-12-03, it means that month and day are swapped! How can I fix it? Thanks.
View 5 Replies
View Related
Nov 7, 2006
Hi,
I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"
SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @TIM_Time)">
<SelectParameters>
<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"
Type="DateTime" />
</SelectParameters>
I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?
Thanks in advance.
View 8 Replies
View Related
Jun 26, 2006
Hello,
I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...
(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))
I want to be able to put 3 months in there somehow...
Thanks in Advance!
View 2 Replies
View Related