Hi,I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:"Select DISTINCT([StartDate]) From [Events]"How can this be done?Thanks,Curt.
I'm currently getting a date in this format 2001-10-08 10:35:45 (yyyy-mm-dd hh:mm:ss) how would I convert the date to this format 2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
I am trying to format a date value as XX/XX/XX when I place a "d" in the format property it formats it as XX/XX/XXXX any ideas on how to change it to XX/XX/XX?
How can I format the current date as yyyy-mm-dd and display it in a text box on the report. The FormatDateTime function only allows certain NamedFormat for the short and long dates but does not satisfy the above requirements. Any ideas?
Helo All - I would greatly appreciate some help formatting the values for my sproc. I need to call a sproc and pass it the values StartTime and EndTime from a web form. The web form uses 3 DDLs for the Start Hour, Start Minute and Start AM/PM as well as 3 DDLs for the End Hour, End Minute and End AM/PM. When I call the sproc, I get the error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Here is the sample column data: 2:06 PM which is stored as nvarchar in the sql db Here is the Sub and the Sproc, Sub Protected Sub btnDateQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDateQuery.Click pnlFiltered.Visible = True pnlShowAll.Visible = FalseDim StartTime As DateTime Dim EndTime As DateTimeStartTime = ddlFromHour.SelectedValue + ":" + ddlFromMinute.SelectedValue + ":" + "00 " + ddlFromAMPM.SelectedValue EndTime = ddlToHour.SelectedValue + ":" + ddlToMinute.SelectedValue + ":" + "00 " + ddlToAMPM.SelectedValueStartTime = CDate("2:07:00 AM") EndTime = CDate("2:07:00 AM")Dim Query_TA As New dalDataFeedsdefsTableAdapters.spFilterExpectedEndTimeTableAdapterDim returncode As Integer = 0 Query_TA.GetExpectedEndTimeData(StartTime, EndTime) Dim Select_TA As New dalDataFeedsdefsTableAdapters.spDynamic_Basics_TblTableAdapterDim dv As New DataView Try dv = Query_TA.GetExpectedEndTimeData(StartTime, EndTime).DefaultView()Catch ex As Exception Response.Write(StartTime + " - " + EndTime) End TryWith DataFeedGridView .DataSource = dv .DataBind() End With End Sub SPROC ALTER PROCEDURE [dbo].[spFilterExpectedEndTime] -- Add the parameters for the stored procedure here @StartTime datetime,@EndTime datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select * From tmpBasics where convert(datetime, ltrim(rtrim(starttime)), 114) >= @StartTime and dateadd(mi,convert(int, duration), convert(datetime, ltrim(rtrim(starttime)), 114)) <= @EndTime order by convert(datetime, ltrim(rtrim(starttime)), 114) END Thanks, Pat
I have around 1000 records each with two dates in a database in MSDE on my PC. I need to move the data to an on line SQL server and have tried to use Microsoft Web Data Administrator to do this. I can export the data from the MSDE to an SQL file but it will not import because the date format in the SQL file is "dd,mm,yyyy".
If I export from either the MSDE or the SQL server both produce files with date in the format "dd,mm,yy" and yet I cannot import either!?!
It is impractical to change all the dates by hand. I am on a budget and do not have access to anything other than free software.
I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end? Thanks
I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view? Thanks
Im sure we have all seen this error before: "The value for the report parameter XXX is not valid for its type"
I have a report (RS 2005) that has 2 date parameters, "start" and "end".
My SQL in not very complicated at all, I have a simple WHERE date between @start and @end
But when I go to view the report in VS2005, I get the above error.
I am yet to find a decent fix for this, what is going on? the date I am trying is: "26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.
I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)
How do I fix this?? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....
I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer I have used the following code (not the conversion function as I don€™t need Hyphen '-') for YYYYDDMM SET @DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) + CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) + CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) )) & for MMDDYY SET @DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+ CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) + SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) )) I am getting the result YYYYMMDD= 200688 MMDDYY =8806 but i want result in YYYYDDMM = 20060808 MMDDYY = 080806 note: I need to convert in integer, finally, caz database data type is integer. can any one give me solution waiting for quick reply
Hello €“ I€™m developing a report that is using SSRS 2005 as the reporting software and Oracle as the database. The report contains a calendar control so that users can select a date range. (DateStart & DateEnd) However, the column that the report is querying against is not formatted as a date, but rather it is a number. (E.g. 12/10/2007 is 20071205 in the table)
I€™ve set up the report parameters with a data type of DateTime and then in the DataSet properties I€™m using the following values:
Then, I€™m using the TO_NUMBER function in the SQL pane around each parameter.
The problem is, the calendar control is only sending a 1 byte number when the month and/or day is not 2 bytes. (E.g. 07/06/2007 is 7/6/2007, but I need to convert it to 20070706)
Does anyone have any suggestions for how to resolve this issue?
I am trying to make a dropdownlist with a date. Where should I define that I do want only the date, not the hour. So far it shows something like "14-02-2006 0:00:00" whereas I just want "14-02=2006". I tried "CAST(CONVERT (varchar(25); Ma_Fecha; 112) AS datetime)" in the SQL Statement but it doesnt seem to work.[CODE]Dim sSQL As String = " SELECT "sSQL = sSQL & " Ma_Date,sDate "sSQL = sSQL & " FROM vwMareaMain "Dim comm As New SqlCommand(sSQL, objConn)Dim dataAdapter As New SqlDataAdapter(comm)dataAdapter.Fill(objDS2, "vwDate")Me.cboDate.DataMember = "vwDate"Me.cboDate.DataValueField = "Ma_Date"Me.cboDate.DataSource = objDS2.Tables("vwDate").DefaultView[/CODE]
Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
I am wondering how to achieve a running count on the rows being displayed in a table list. Not sure how to get it to show 1 for first row, 2 for second row, 3 for third row and so on. Example
No | Name 1 | John 2 | Jane 3 | Jim
I am also wondering on the date format in SSRS. When I try to format the date 10/31/2007 in a DD/MM/YYYY format it does not come out so good. Basically I go to the cell property and choose custom formatting, input the formatting string d/mm/yyyy but it only shows 31/00/2007. Can you guys tell me what I am doing wrong?
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01 e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'
Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:
cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)
...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -
So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT  DISTINCT  dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] .... Â Â Result: Need to Display [Total Service Time] in below Format:
In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'
This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000
So I amended the SELECT query to:
MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'
This brings the date through as 11/06/2015, which is preferable.
But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.
Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?
Hi All I have seen several posts on this issue but as yet no answer. So i'm hoping that somewhere out there knows what the issue is.
SSIS package queries database creates MS spreadsheets per customer and then emails each customer their list. Anyway all works fine apart from the fact that the Excel Destination Editor converts the date from uk to us format. My routine is already using an MS Excel template (correctly formatted) which it copies as part of the package. Any ideas anyone?
I hate access, I wish we could get away from it. I cant view employee pictures anymore, and every time theres and upgrade to a different version that means different problems. We are going to upgrade from 2003 to 2007. Good grief SQL doesnt need access, its too good for it anyways. Theres gotta be some different software out there that I can change too. Its just that I'm not a programmer. I keep hearing about Visual Fox Pro, that its easy but I have no idea. Anyone got any suggestions??
I have setup SQL Reporitng Services on Windows 2003 server. Environment is like this on server: - Framework 2.0 - SQL Server 2005 Enterprise Edition (with SQL Reporting Services) - IIS 6.0 - Internet Explorer 7 - Microsoft Ajax 1.0 - Active Direcotry
I have configured SRS and it was done successfully, I got two Virtual Directories (ReportServer and Reports).
My problems are like this: - When I type http://localhost/Reports in browser, It display ReportBuilder page with "Home" hyperlink. Nothing else is displayed on this page!!! (I am able to execute http://localhost/ReportServer/ I have login as Administrator on my server.)
- I created SQL Report Project in Visual Studio 2005 and created a Shared Data Source and a Report, I am able to preview the report in IDE. Then I tried to build and deploy it on above server, For it I modified project properties and changed TargetServerURL to above server (I tried following combinations for this property without any success http://192.168.115.17/ReportServer / http://ss6/ReportServer/ / http://localhost/ReportServer).
Exact error message is: A connection could not be made to the report server http://192.168.115.17/ReportServer/.
Additional Information: The attempt to connect to the server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
What could be the problem and solution? SRS is already running.
i have a severe problem here regarding production DBs
the system heavily utilizes UDFs in queries in bad behaviour
being used in select and where clauses , the queries can take considerable time
i have managed to enhance issues regarding indexes and select clauses , but UDf in where clauses still a problem bcz they contain some logic i cann't make it inline
here is an example : select col1, col2, col3, dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) , dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice) ,from listing inner join ResidentialSeller on listing.id=ResidentialSeller.idwhere (@TotalAreaFrom is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) >= @TotalAreaFrom ) and (@TotalAreaTo is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) <= @TotalAreaTo ) and (@SalePriceFurnishedFrom=0 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)>=@SalePriceFrom)) and (@SalePriceFurnishedTo=1000000000 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)<=@SalePriceTo))
the two UDFs fn_ConvertUnit is used to convert from measure unit to another (like from Meter to feet)
and fnCurrencyConvert is from currency to another (like from $ to Euro) based on predefined rate
CREATE FUNCTION [dbo].[fn_ConvertUnit] (@Uvalue float,@CfromID tinyint,@CToID tinyint)RETURNS float AS BEGIN if ((@Uvalue is null) or (@CfromID is null) or (@CToID is null))begin return nullend declare @fac intdeclare @temp floatif (@CfromID=@CToID)or (@Uvalue=0)beginreturn @Uvalueendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac-- set @temp=@fac end if (@temp<@Uvalue) begin-- return 1.79E10return 1000000000-- A Big number enough to be compared by any number user can type end else begin RETURN @Uvalue*@fac endendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@Uvalue) begin return 0 end else begin RETURN 1/(@Uvalue*@fac) endendreturn -1END ------------------------------------------------- -------------------------------------------------
CREATE FUNCTION fnCurrencyConvert (@FromCurrency int, @ToCurrency int, @Country int, -- This prameter is useless @AmountOfMoney float)RETURNS floatASBEGINif( (@FromCurrency is null ) or (@ToCurrency is null) or (@AmountOfMoney is null))begin return nullenddeclare @temp float -- will carry the max value without exceptionif @FromCurrency=@ToCurrency or @AmountOfMoney=0beginreturn @AmountOfMoneyenddeclare @fac floatif exists(select * from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 1.79E308 end else begin RETURN @AmountOfMoney*@fac endendif exists(select * from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 0 end else begin RETURN 1/(@AmountOfMoney*@fac) endendreturn -1END
i will appreciate any help regarding these two Udfs as they r the most problematic ones , other suggesions r highly appreciated
Hi everyone. I'm new to all this, and having a nightmare accessing my database. I'm using IIS 5.1 a desktop sql server (MSDE) and Visual Studio.NET Enterprise Architect 2003. If I try to access a sql database, I get an error "Login failed for user (ServerName)ASPNET". If I try to use an Access database, it tells me that the file is already opened exclusively by another user or that I need permission to view it. From looking through the forums, it appears to be a security thing (an account for ASPNET?) but how do I fix it. In very plain English...
Hi There, I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page: Execution of a full-text operation failed. A clause of the query contained only ignored words Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words. In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far. Many thanks Stuart
Table tblStudentClass stores all information about classes that student was in, and will be in. So there are plenty of records with the same lndStudent field. What I need is to pull out information about all students from table tblStudent, and the last classes the were/are in, or will be in if they are future students.
So the needed output should in this form with no duplicates ------------------------------------------------ |anStudentId|txtFullName|txtMobile|txtClassCode| ------------------------------------------------
What I have so far is this query:
SELECT tblStudent.anStudentId,tblStudent.txtFullName,tblStudent.txtMobile,tblClass.txtClassCode FROM (tblStudent INNER JOIN ( SELECT tblStudentClass.lngStudent, MAX(tblStudentClass.lngClass) AS LastOflngClass FROM tblStudentClass GROUP BY tblStudentClass.lngStudent ) AS s3 ON tblStudent.anStudentID = s3.lngStudent) INNER JOIN tblClass ON s3.LastOflngClass = tblClass.anClassID
WHERE tblStudent.txtMobile LIKE '447_________'
ORDER BY tblStudent.txtMobile;
Which is not exactly what I want because it aggregates by greatest classid, not by latest date, and it is slow on very large dataset. Maybe there are ways to optimize it (temporary tables,stored procs,views)?
