i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:
CREATE VIEW [billy.bhuj].[bo current month]
AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]
FROM [dbo].[bo]
Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))
the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.
quote:Run the view for orders shipped from January 1, 2003 and June 30, 2003, formatting the shipped date as MON DD YYYY.
creating the view:
CREATE VIEW vw_orders AS SELECT o.order_id, c.customer_id, c.name AS 'customer_name', c.city, c.country, o.shipped_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
running the view:
SELECT * FROM vw_orders WHERE shipped_date between '01/01/2003' and '06/30/2003' ORDER BY 'customer_name', 'country'
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
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
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?
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value from my_table where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
Hello, not sure if this is in the right forum or not...
I have a .DTA file from Sage which is a Database file, im assuming anyway, how can i convert it into a format where i can view it in say access or excel, something like that, or anything really just so i can read it all properly... i can read it in word - buts of it but its all mangled and awful...
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,UCASE(Buildings.BuildingName) AS BuildingName,Buildings.MasterPlanCode, Buildings.UniformBuildingCode,Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, Buildings.YearLatestImprovement,UCASE(Buildings.Address) AS Address, Buildings.CityCode,CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,Format$([Mechanical],"0000000") AS dMechanical,Format$([Custodial],"0000000") AS dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
problem with transact-SQL (SQL2k). I'm gathering informations from2 different tables and the column that interest me (the invoice date) have same format but not filled in the same. One is an old table and the format is "datetime" (but written like dd/mm/yyyy) and the new one "datetime" as well but "dd/mm/yyyy hh:mm:ss" (and it's not small datetime) I'd like to convert the date format wich appears with the "hh:mm:ss" to a smaller format (just dd/mm/yyyy). I'v tried with the conversion into text (the CONVERT function limits) wich works but I need to deal with a date format when I get the result. If I re convert to smalldatetime, I get the hh:mm:ss again.
I have successfully converted datetime fields that hold the date and time to show just the date using CONVERT like this
CONVERT(DateTime,MyDateField,103)
However, in another table just the date is stored in the SQL table the sp below displays the date with time shown as 00:00:00
I am unable to display just the date, even if I use CONVERT on it as above.
CREATE Procedure [spRMU_CountRequests]
AS
SELECT DateRequested AS Date_Requested, COUNT(*) AS No_Requests FROM tblFileRequests WHERE DateRequested >= '01/01/07' GROUP BY DateRequested ORDER BY DateRequested DESC GO
I need to convert the date below to the SQL datetime. The single month dates do not start with a '0'.Can anyone please provide me with a script to do this? Thank you in advance!
ex: 90106 120106 10107
needs to be converted to: 2006-09-01 2006-12-01 2007-01-01
Good Afternoon, I have posted here before and have gotten excellent feed back from you SQL Gurus! I would have to say that date converts are my kryptonite! My problem is that I have multiple accounts that are tied to multiple dates on a server. I need to pull the MAX(date) of the account numbers; however, the date is formatted MMYY. For example;
If I select the MAX date for Account 123456 it will give me '1206' when clearly '107’ is the most recent date. Can anyone provide me a script that will format the date so that SQL will understand what I am trying to accomplish? Also, the day is not important; it can be defaulted to 01. - Thank you very much
I have a variable that I am passing to a stored procedure from apropritery software application. The variable it passes is the numberof days that have passed since 1/1/1900. I cannot seem to locate aconvert statement that is applicable. Any guidance would be greatlyappreciated.Thanks
How does one convert to a Date from this string that represents a Date?
'2007-02-28T01:11:12+10:00'
In fact what is the time stating? is it 11:12 or 10:00? But more importantly, how do you convert it to Datetime Datatype to Insert into SQL Server 2005?