I've got a table where the date and time fields are all out of sequence and I need them in sequence when I am extracting data from the table, but I'm having trouble writing the SQL.
I've got this line which sorts the date and time for the table:
SELECT * from tblcodegreydiv order by date desc, time desc
Now I'm trying to retrieve the closest record prior to a date,time selected by a user...
SELECT * from tblCodeGreyDiv
WHERE recid=(SELECT MAX(recid) from tblCodeGreyDiv WHERE date + '' + time <= '2007-09-19 05:24')
I need to somehow sort the table before I run the above sql statement. How would I go about doing this?
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
I am trying to design a query that will return all the records based on a single date but because of the date/time data type in MS SQL 2005 it defaults to the date and the time 00:00:00 which will return no records for me.
Is it possible to ignore the time part in the select statement?
Hi NG,i've got a mystic problem i can't solve, perhaps one of you has a good idea...i will explain with some code, so fo better understanding my probroblem:i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.following Stored Procedure is ok and does it's job fine with "EXECUTEIntDayView"CREATE PROCEDURE dbo.IntDayView ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)AND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)but now, i want to have variables for the Date string, so i tried followingsyntax:CREATE PROCEDURE dbo.IntDayView @DayViewf DATETIME,@DayViewl DATETIME ASSELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,Ergebnisse.Bewertung, Ergebnisse.BemerkungFROM Interviews, Mitarbeiter, ErgebnisseWHERE (Interviews.DatumZeit >=@DayViewfAND Interviews.DatumZeit <=@DayViewlAND Interviews.User_Nr=Mitarbeiter.User_NrAND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)--------EXECUTE IntDayView@DayViewf=convert(datetime,'2004-02-10 00:00:00',120),@DayViewl=convert(datetime,'2004-05-16 23:59:59',120)so i tried the convert in execute, and also in the Procedure, but both times anerror occures Error Nr. 156, or 8114,so something with the convert is wrong but, also if i do it without convert itdoes not work.So how to select by time, if this doesn't work?????Any suggestions??Please help me,thanksJan Schmidt
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July DressageJuly Jumping July Led in July Led in September Led in May DressageApril DressageJuly Flag and PoleJuly SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT (TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Hi All, I've looked around but can't seem to find an answer for this. I have a cube that has a fairly large time dimension (going back to 1949) as the data demands this. When a user is browsing the cube and applies a filter or adds the a time heirarchy as a value it's always sorted from oldest to newest. Whilst the need is there to look at data from 1949 most people want to look at the last few years. The problem is they have to scroll right down the list to get to this. Is there a way of having the most recent years of the time dimension appear first in these lists to make them more accessable?
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
Hi, I hope I can explain what I am trying to achieve (in MS SQL Server): The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name. I have tried: ORDER BY date, surname, forename (gives dates in right order but not names) and ORDER BY surname, forename, date (gives names in right order but not dates)
When the autogenerate property of datagrid is set to true, how to perform sorting on date fields.
Example:
SELECT DC_NO AS [Document No],CONVERT(CHAR(10),DC_DT,103) AS [Document Date], [Name] FROM XYZ
Here we convert the date field to the format "dd/mm/yyyy" so when sorting is done it is done in the format "dd/mm/yyyy". But instead sorting should be done in "yyyy/mm/dd" format.
i am pulling data from a read only database and the dates are i think in varchar format (they sort by 01-01-2012, 01-02-2011...etc)the dates are also in US format.how can i convert these to a EUROPE date format and also datetime so that i can use the > and just show logged events greater than a specific date
Code:
SELECT logId, logdate, logdesc FROM dbo.log WHERE (logdate >'07/07/2012')
the code about will not work as the logdate is in varchar.
We've just installed SQL Server 2000 on one of our servers and havenoticed a strange behavior. When clicking the column headings in thejob display in Enterprise Manager, the list is sorted (first clickascending, then descending) on the column clicked except for "Next RunDate." When we click on "Next Run Date," the list is reordered, butrandomly. Each time we click it we get a different order but neverascending or descending. This is true whether we're at the server oron a remote machine. We never saw this behavior with SQL Server 7.0.Any ideas? Thanks!
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.
I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM
Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:
1/18/2005 1:03:19 AM
get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.
Anyone know what I can do to get this sorted correctly?
I want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today. I've tried something like this but not working
Hi all, I have a SELECT DISTINCT and sorting problem. I work at a college and our department has resources such as CD players, Projectors etc that staff can book for a certain time and room. The table I have for these bookings records multiple records for each booking because I’m recording the time slots as well. It’s a bit like this: BookingID: 1, RefNo: 234543, ResourceID: 5, TimeSlotID: 4, TheDate: 04/05/2005BookingID: 2, RefNo: 234543, ResourceID: 5, TimeSlotID: 5, TheDate: 04/05/2005BookingID: 3, RefNo: 234543, ResourceID: 5, TimeSlotID: 6, TheDate: 04/05/2005BookingID: 4, RefNo: 234543, ResourceID: 5, TimeSlotID: 7, TheDate: 04/05/2005 That would be one booking but across 4 time slots, relating to actual times during the day. I realise this probably isn’t normalised but I’ve done it now and ran into a problem. I need to select distinct on the RefNo column to return only one record for each booking, but I also need to sort the data by the date and time, so that the today’s are at the top, and in time order. Without doing a complete redesign of the data structure, can anyone help? Thanks, Steve
I want to select the below info from a sql 2005 db, to use it in a dynamic datadriven menu. Only problem, I don't know how to formulate my select statement. So here's the data
ID, TEXT HREF DESCR PARENTID
1 hMenu1 link1.htm desc text 0
2 hMenu2 link2.html desc text 0
3 sMenuM1 link3.htm desc text 1
4 sMenuTSMenu3 link4.htm desc text 3
I want the data structured so that below the row with id 1, would come it's subitems, subitems for the subitems and so on. So the recordset I'd like to return would in this case be:hMenu1 sMenuM1 sMenuTSMenu3 hMenu2 I've indented the data just to make things clearer. And finally, I want the substructure to be infinite at least in theory.I also do not want to use recursive logic to do this, if possible. I can change the structure of my table if neeed.Cheers!/Eskil
I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.
Created a custom SSRS report. applied an interactive sort for a date field. I have the the formatting set to "MM/DD/YYYY" and it sorts everything correctly except for the year.
Example:
As you can see, the dates with 2014 aren't sorted correctly. I want it to sort the dates like:
11/06/2014 12/10/2014 01/07/2015 etc, etc.
Is there a way to get this to take into account the year? but also the month and day?
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
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?
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
