I have a timestamp in one table that is of datetime type and I need to move it to a table with a Varchar(30) type. What is happening is a date that should be set up like this "11/12/2004" is actually coming across as "Nov 12 2004". I tried using a cast(fieldname) as Varchar(30) on the datetime type, but it didn't help. does anyone have any ideas on how to retain the original date form in moving it to a varchar type? thanks in advance...
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?
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,
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
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.
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?
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)
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?
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
I have inherited an SQL server that is running out of disk space. SQL 2000 was installed on a regretfully small C: partition, and I need to move as much as I can to the D: partition. I really don't want to uninstall and start over, and I am hoping there is a whitepaper or something that I can use to accomplish the same thing.
My company recently purchased a new ERP system which does not support 64 bit SQL Server. For this reason, I need to uninstall the 64 bit SQL server 2005 and install the 32 bit version. This leads to my question. I have an application currently using a database on the 64 bit version and would like to confirm that the DB will work correctly in the 32 bit version. Do I just have to detach the database, copy the files, then install SQL server 2005 32 bit and attach the DB?
I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the errorSystem.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()at System.Data.OleDb.OleDbConnection.Open()at DreamweaverCtrls.DataSet.DoInit()Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me. My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.1. A good book to follow and easy to follow for SQL (connections / how tos) or website2. Good examples or book on "ASP.Net Enterprise Manager"3. How to convert access databases into SQL easy (some have over 50,000 listings)4. how to convert Tab files into SQL easy.Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.Thanks again,Rusty
Hi I want to transfer a database from one PC to another. (Both running SQL Server 2005 express) I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder). I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?
I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
Hi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.
I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.
I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar. I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server. What do the best way to to this? PaulTucson, AZ
Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?
What is the best way to move the databases to a new hard drive connected to the system? We added a new larger hard drive and I would like to move just the databases to the new hard drive. Thanks
I have an SQL database running on an old server and i am upgrading the server, but i am having some problems moving my SQL database over. Has any one got a work through they could mail me to give me a hand!!
I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
Hi I have a database(CEB) and my CEB.mdf is on D Drive and CEB.LDF is on G DRIVE ...NOw I want to move the CEB.LDF on to the different drive .. can any one suggest me the way and will I have any effect on the database. It is kind of urgent.
I want to move the physical location of the msdb database on a SQL server. However I cannot drop the database with the drop database statement, even setting it to dbo use only and single user mode. I get an error message saying the database is in use. Obviously I do not want to reinstall the server. The reason I want to move it is because it is located in the default dir on the C: drive which is running out of space.
I have a database approximately 30 GB in sixe which need to be moved from one SQL server to another. Does anyone know the most efficient way of doing this, other then backing up to tape?
Does anyone know how to move or copy DTS Package? That I need to do: I would have to move database from one server to the other and last thing I want to do is recreate DTS packages from scrach. I could not find any way of transfering DTS packages.
I need to separate the data and indexes onto different drives.Currently they r all on same drive..Now i need to move indexes onto a separate drive.There are no file groups now except the default.
I plan to create a file group on the new drive.I know if i Drop clustered indexes and recreate them on the new file group,it will separate the data pages from index pages.But the data pages need to be on the same drive as it is now.So i need to move index pages or non-clustered indexes only on to the new file group on the new drive.Even this can be done by choosing a different file group for the non clustered index in Enterprise manager.But this very cumbersome if there exists many tables as is the case now like 300 tables..
My Question is,
1.Is there any way to script only indexes so that it includes drop and create commands to be created on new file group???
2.Is there any other way to do this..
Please let me know if any body worked on this before..I really appreciate if some body can give me info on this..
