I couldn't think how to title this post so I apolgise for the poor title, maybe it's because it's getting late in the day and is too warm, maybe thats also why I can't think of the answer myself right now .
Anyway say I'm doing something where part of the data is a date (e.g. a counter) and then I want to get all the data from there between a set of two dates.
Now I can do that all fine, however what I want is 0's to be returned for dates where there is no data stored.
E.g.
At the moment I would get (for dates between 1st and 7th June):
Now as this DB should have data in it for every day of the year (but not always matching the where clause) I thought I could get the count back using a sub-query and a coalesce, but that seems rather hacky to me.
I'm on MS-SQL Server 2000, any help would be greatly appreciated.
I've been having this huge problem with date and SQL 7, like lot of people. I use asp with the sql and trying to do this query where startdate should equal or smaller the date today, here are to queries that worked, but not anymore.
SELECT * FROM tbl_date WHERE convert(varchar(10), startdate, 101) = convert(varchar(10), getdate(), 101)
this query worked until the year 2000, the normal y2k bug then this one, wich i saw here on messageboard:
select * from tbl_date where startdate =< '" & date & "'" well this worked until today, when it just stop working
I'm trying to insert a date, I don't want the current time. It would be ok if it was 12:00:00. I have tried using the below bold area, but I get an error that the conversion is not correct. Any ideas? INSERT INTO tblpayments (rec_ID,client_ID,tranDate,tranAmount,DateEntered,EnteredBy) SELECT rec_ID,client_ID,Convert(varchar(5), GETDATE(), 10) AS TranDate,Tranamount,DateEntered,EnteredBy
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
I am new to SQL; the following code is supposed find comments that are not null on one day before the studydate.
SELECTp.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments FROMStudies s INNER JOIN Patients p ON s.PatientID = p.PatientID WHEREs.Comments IS NOT NULL AND s.StudyDate >= CONVERT(char,DATEADD(day, -12, GETDATE()),101) AND s.StudyDate < CONVERT(char,DATEADD(day, -11, GETDATE()),101)
Is there a problem with this code? I expect there is because when I ran the query it didn't return any results even though I am positive there are comments in the database one day before the studydates.
HiI'm having problems following the tutorial on creating a data access layer - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page. <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle"> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines... using NorthwindTableAdapters; <<<<<this is the problem - where does this come from? protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }Thanks in advance
A little background first. I have a header table and a detail table in my staging area/ods. I need to join them together to flatten them out for load. The Detail Table is pretty deep - approx 100 million rows.
If I use the setting (table or view) and set the table name (say, the detail table), the package starts up nicely. But if I switch the OLE DB Source to using a SQL Statement and then join the tables in the SQL, then the Pre-Execute phase of the package takes a VERY long time. I have waited as long as 30 minutes for this phase to complete, but it never finished.
Another twist...If I take the join select statement out of the OLEDB Source and put it in a view on the server, then swith the OLE DB Source to look at the view using the (table or view) mode, then the package gets through the Pre-Execute phase just fine.
Can someone go into detail as to what the Pre-Execute phase does and why a deep table might make it take a long time? I know already that the pre-execute phase caches the lookups, but not much else.
I try to find the way to create editable data grid (a view from MySQL database) in a web page. So, user can edit, add, or delete from one page instead of having switch edit, insert pages, one field in the view only. Any script I need to write or any special server behavior I can use?
Hi there, i have sometimes trouble to write data to a table in a sql server expr. database.
i have a vb.net application that writes every 2 minutes data to a table. sometimes (no regulary phenomen) the error appears after some hours, sometimes after some days. then i cannot write data in the table. it seems that the database sleeps or is offline.
before i write the data i check if the connection is open and i can write data. if i cannot write the data i try several times and often after the third try i can write the data.
i have checked everything (no power save's on or other power off modes)
i have also test it with SqlClient.SqlConnection.ClearAllPools()
and many other things to find the bug, but no success at the moment.
it seems that the database or the table is away ?? or sleeps for some seconds ?!? i have no idea.
I have to import a flat file with commas and double quotes as the text qualifier in a SSIS package. However, when I try to import the data into a table, the data moves the information to right, therefore, the last field will capture mulitple fields worth of data. When I create the Connection Manager for the flat file, I have the format as [Delimited] and Text qualifier as ["]. I do not check the unicode button, but under Advanced, make each field a Unicode string [DT-WSTR]. I have included a sample of my data below.
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) SELECT
1 AS KPI_OWNER_ID , 23 AS METRIC_ID , .75 AS GOAL , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
drop table #temp create table #temp (id int, dat datetime) insert into #temp values(1,'2015-03-01') insert into #temp values (2,'2015-02-01') insert into #temp values (2,'2015-01-01') insert into #temp values (3,'2015-01-01') insert into #temp values (3,'2015-03-01') insert into #temp values (3,'2014-12-01') insert into #temp values (2,'2014-12-01') insert into #temp values (1,'2014-12-01') select * from #temp
The result i am expecting is 2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result.
Hello All! Can anybody tell me how to add a date data type into SQL Server? I have a web application that collects data from users, and one of the data collected is the date. I would like to save that date as a date only and not as a datetime like SQL Server wants me to. My first understanding is that, since SQL Server only allows a datetime data type, this is why it appends a time whenever a date is saved in or it appends a date whenever a time is saved in. How do I accomplish this task of saving a date as a date only or a time as a time only? Is there a way to add a date or time data type w/in SQL Server? Or SQL Server just doesn't allow for it? Any help is appreciated!
i really need some help from all of you. I currently have some DTS package which will transfer data from text file to the SQL 7.0 This transformation is schedule using the Sql Agent.
The problem is the transfering data which have the Date datatype. the Date format is in the dd/mm/yyyy. But the default language i set in my sql server is english which suppose will use the date format mm/dd/yyyy.
therefore, the Sql server will treat my data which in dd/mm/yyyy as mm/dd/yyyy. so, the data like '08/09/2000' (in dd/mm/yyyy) become '08/09/2000' (in mm/dd/yyyy)
i already set my user'language who will login in the sql agent as 'british english'. I also the OLE connection in the DTS package that i built using 'british english'.
when i run the agent in the Sql Enterprice Manager, it use the dd/mm/yyyy format which is correct. BUt when i log out and let it run as services which mean let the agent excute the DTS. it use back the mm/dd/yyyy fromat which cause the data become wrongs.
i hope someone can give me some advice on this. Thanks for your help
Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??
My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8) 2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7) 3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
The following is the sql being executed in my Crystal report. There seems to be an issue with the same date request. Sometimes it shows data, other times not. We have data every day, we are a mass market company. Is there anything i can do for the sql to do select when from and to dates are the same?
SELECT "OEHIS1"."ODORD#", "OEHIS1"."ODORDT", "OEHIS1"."ODNTU$", "OEHIS1"."ODSHP#", "ICPRT1"."IARC11", "OEHIS1"."ODORDD", "ICPRT1"."IARCC4", "OEHIS1"."ODQTY#", "OEHIS1"."ODRQSD", "MFHHMH"."MHAWGT", "OEHIS1"."ODPRT#", "OEHIS1"."ODPRLC" FROM ("S10M10"."ASTCCDTA"."EODDETAILS" "OEHIS1" INNER JOIN "S10M10"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEHIS1"."ODPRT#"="ICPRT1"."IAPRT#") INNER JOIN "S10M10"."DLIB"."MFHHMH" "MFHHMH" ON "OEHIS1"."ODORD#"="MFHHMH"."MHORDP"
I have a query that pulls data by date so that I have every day of the month but I would like to group it by month so that I only have one entry per month. Can that be done?
I am really new to this. Is there a way to set up a data type in SQL Server to automatically write the current date to a column? Instead of sending it from the page, the DB just writes the date to the column ...
I have an accounts table in which i have a date, credit,debit,balance columns.I gave the datatype for date column as datetime.But after entering the values,Date is displayed in a different format.How to display the date in our specified format.
And also,in the balance column i want to store the balance.amd when ever there is a credit or debit,i want to add or subtract from the balance correspondingly.How do i manipulate this .
I have a table with a field "StartedAt". I wish to capture all the data in that table which has Yestarday's StartAt date. My script below captures the data which has yestardays "StartedAt" info as well as today's date till now. How can i capture only yestardays info only.
SELECT StartedAt FROM myTable WHERE StartedAt >= DATEADD(day, DATEDIFF(day, 0, getdate()), -1)
Hi, I created a function class in "Script Components" to convert an Integer to a Date data type. It works good except that it converts NULL value to the date 1988-12-31; but I want to return NULL when the value is NULL. Any idea on this?
Here is my function:
Public Class ScriptMain Inherits UserComponent
Private Function ConvertToDate(ByVal dateIn As Double) As Date Dim DateStr as String ........... ........... If DateStr Is Nothing Then Return Nothing Else Return CDate(DateStr) End If End Function
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.ReportDte = ConvertToDate(Row.RPRTDT) End Sub End Class
I need to convert an integer value fo for example 1071123 to a date field. This value would represent 107 = Year 2007, 11 = Month November, 23 = 23rd day of month. So effectively this would translate to 2007-11-23 00:00.000.
I would like to do this in the Integration Services package. I am retrieving data from an AS/400 system to an SQL Server DB. I'm not sure if I can do this with the Derived Column object or is there a better way to achieve this conversion.
Please, can anyone shed some light on this for me?
Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.
Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error: Server: Msg 8630, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor encountered an unexpected error during execution.
Here are some of the dates that were present in the table: DateColumn1 ----------- 1900-01-01 857:44:45.813 1900-01-01 872:51:16.427 1900-01-01 872:54:57.440 1900-01-01 873:09:32.107 1900-01-01 873:13:10.560 1900-01-01 873:16:49.867 1900-01-01 888:27:00.640
I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)
I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.
However I cannot seem to be able to convert and store (in SQL) this column can anyone advise
