How To Manipulate Dates
Aug 4, 1999
Is there a way to manipulate a date variable into a specific month and day?? For example, I want whatever date the user will enter to be
changed to June 30 of the following year. So, if the variable @date is 12/12/1999, I want to change it to 6/30/2000. If the date
is 2/1/2001, I want to make it 6/30/2002
I can't use DateAdd because you need to know how many months to add or days to add. Basically, how do I retain the month and day while changing the year
the year based on a dynamic field. What I need is a DateChange function that is DateChange(mm, @month, @date) ? Is there
something like that?
Thanks,
Joyce
View 2 Replies
ADVERTISEMENT
Aug 1, 2007
Hey everyone,
So I'm querying from a database where one of the fields for each entry is a city. I would like to count the ones in each city. So I'm counting and grouping on city. Unfortunately, the data is not very consistent. Some are listed as Miami, FL and others simply as Miami. I don't need the state so I can just drop it, but I can't figure out how. I was using charindex and substring, but can't get it to work because if there is no ',' then the charindex is zero and substring doesn't work correctly. Any help would be greatly appreciated.
Thanks,
Keith
View 1 Replies
View Related
Jan 10, 2007
hi guys.
i want to know if is it possible to connect various databases with only one connection?
thx.
View 6 Replies
View Related
Apr 12, 2005
Hil,
I posted a thread here: http://forums.devshed.com/showthread.php?p=1061405 but was told it might be more useful for me to post something here... should have thought about it to begin with!
The picture is in that thread so do please look at it! http://forums.devshed.com/attachmen...tachmentid=7431 should be the URL.
As you see the top 3 records start and finish at the same time, as do the last 3 records. This can then also be repeated for the next day etc etc. What I want to happen is that the top 3 courses to appear on a single line and the 2nd set of 3 to appear on a single line also.
The main SQL to retrieve the records is as follows:
Code:
sqlqry = "SELECT * FROM t_sessions a INNER JOIN t_session_times b ON a.session_id = b.session_ID"
sqlqry = sqlqry & " WHERE right(left(convert(varchar,a.session_date,113),11),8)='"+displaym +"'"
sqlqry = sqlqry & " AND a.session_supplier_id=61"
sqlqry = sqlqry & " AND a.session_status ='A'"
sqlqry = sqlqry & " ORDER BY a.session_date, b.session_start, b.session_end"
then while this recordset hasn't come to an eof(end of file) I execute:
Code:
query = "SELECT course_name,outline_name"
query = query & " FROM t_course_ref"
query = query & " LEFT JOIN t_it_outlines ON t_course_ref.course_id=t_it_outlines.course_id"
query = query & " WHERE t_course_ref.course_id="+cstr(rs.fields(1).value)
Thanks!
View 5 Replies
View Related
Nov 14, 2006
I remember I was able to view, add, alter table on the mdf in either sql express 2005 or in vb.net 2005. But right now I can't do either, what do I have to to change
View 1 Replies
View Related
Jun 7, 2007
Hi,
i want to programmatically manipulate the property 'Name' of a ControlParameter inside a InsertParameters tag.
This the aspx code: ------------------ <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<% ......... %>" InsertCommand="INSERT INTO .......)" <InsertParameters> <asp:ControlParameter Name="myname" ControlID="na" PropertyName="text" /> </InsertParameters> </asp:SqlDataSource>
code-behind: ------------
Dim a As String a = SqlDataSource1.InsertParameters.Item(0).ToString
but 'm stuck here
Thanks for help
Tartuffe
View 1 Replies
View Related
Apr 21, 2008
hi,I have a page Price List with GridView to display only two columns of a table (Product name & price). Page size=20. I use SqlDataSource for it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas? Thanks.
View 2 Replies
View Related
Oct 15, 2006
what re the sql functions to manipulate numbers, decimals, dates..
1/ like if I have 123443.78654 I want to display just 2 decimals : 123443.78 or 3 decimals ..
2/ also if I want to have bankers rounding
3/ how about dates conversion : from string to date type and vice versa and adding dates, substracting dates, getting the day, the month....
what re the SQL functions to do that pls
View 4 Replies
View Related
Dec 5, 2007
Good morning, everyone. Maybe I'm just having a brain fart, but I'm totally new to SSIS (I dabbled very little with DTS in the 2000 days) and cannot for the love of me figure out how to achieve my goal with it:
My company needs to extract data from a variety of sources; tab-delim files, Access databases, other SQL tables and the like. I know how to do this. However, I need to perform data manipulation queries on this data before I place them into SQL tables, as I want to avoid having umpteen temporary tables that I'll need to add checking for. My predecessor did everything in Access, and has a 76-step process (yikes!) that basically will grab all the data, do some minor manipulation, and plop it into a temp table (this is still in Access, not SQL), then repeat the same thing dozens of times.
To give you an example, here's a sample of what I want to do:
- Extract several columns of data from a tab-delimited file on the local drive. This I know how to do already.
- Perform some data cleanup and manipulation functions on this data (specifically, obtain the lowest value out of three columns, with the added caveat that I make sure it's not zero to begin with). I have the SQL code for this already written.
- Store the results of this data somewhere, so I can pull it and apply additional logic to it; for example, take the lowest value I've retrieved, and update the corresponding column in another database table with it.
Basically, is there any way to avoid the use of dozens of temp tables? There's a lot of data which needs to be pulled in, manipulated, and spit back out to be manipulated by something else a little later on, and the way my predecessor did it was, as I said, to use dozens of Access "Make Table" queries for every minor thing. It's not a big deal if I need to do it, just I'm trying to consolidate the steps needed, as the old way is very inefficient. I've been at this job a month and I'm still trying to wade through all of his queries to discover just what they do, and look into combining several of them.
Forgive the slightly newbish question, but as I stated I've not worked with SSIS really. I'm in the process of learning it better, as I'm sure it can fit our needs.
View 5 Replies
View Related
Dec 22, 2006
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
View 1 Replies
View Related
Feb 7, 2007
After generating one of my reports, I process the XML output through an XSLT stylesheet and export it to a text file. The issue is that after the export, the generated output text file begins with the special Byte-Order-Mark marker "EF BB BF" standard to Unicode files encoded in UTF-8, UTF-16 or UTF-32. I have explicitly set the attributes of the xsl output element to <xsl:output encoding="us-ascii" media-type="text/plain" method="text">, but it seems as though those are ignored when the output file is written. I cannot have these characters, because I am generating a fixed-width file for input into a legacy system.
Any suggestions or thoughts on what is causing the BOM to be written to my file, even though I have set the encoding to be different than UTF-8?
View 1 Replies
View Related
May 7, 2007
Hi,
I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.
These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.
It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.
What are the options available for me to be able to query and differentiate the 'deleted' records ?
Thank you in advance,
Bertrand Larsy
View 6 Replies
View Related
Mar 4, 2008
I have somw tables like Product, Sales, Customer.
I used UDTs like ProductCode = nvarchar(30)
CustomerCode= nvarchar(15)
How can I modify my UDTs ?Is there any quick way for that ?
****
Thanks.
View 3 Replies
View Related
Jun 4, 2015
I have one column CandidatePlaced (Data type Boolean).
Using data conversion i changed data type to DT_WSTR and then i used derived column to manipulate the data. Ex. 1 = "Yes" and 0 = "No"
[Candidate Placed ?] == "1" ? "Yes" : "No"
But at end of result i got all the columns as No. Some should be Yes.
View 7 Replies
View Related
Jul 3, 2007
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
View 1 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
Sep 23, 2013
I am trying to manipulate the data being imported from another data source. See below:
I need to make an IF THEN statement: If KeyDate =< 01/01/2013 THEN STATUS = 'Disposed'
Notice I am adding data in the last column as everything is 'Active'
SELECT dbo.UserConfig.Id,
dbo.UserConfig.ServerConfigId, dbo.UserConfig.DisplayName,
dbo.UserConfig.UserName, dbo.UserConfig.MailboxSMTPAddr,
dbo.UserConfig.OverrideSMTPAddr,
[Code] ....
View 3 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
