how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value
Code Snippet INSERT INTO @TempItems (OrderID) SELECT OrderID FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID ORDER BY CASE @SortOrder --Order ASC WHEN 0 THEN cast(OrderID as varchar(100)) WHEN 1 THEN cast(c.CompanyName as varchar(100)) WHEN 2 THEN cast(e.FirstName as varchar(100)) WHEN 3 THEN cast(o.OrderDate as varchar(100)) WHEN 4 THEN cast(o.RequiredDate as varchar(100)) WHEN 5 THEN cast(o.ShippedDate as varchar(100)) ELSE '1' END,
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2 oct 3 sep 13 sep 21 sep 22 sep 30 aug 3 aug 5 aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
I have below SQL, which should be order by posteddate ASC
SELECT AnnouncementID,[Subject],[Description], CONVERT(nvarchar(10),PostedDate,101) AS PostedDate, CONVERT(nvarchar(10),ExpiredDate,101) AS ExpiredDate, CountryID,CreatedBy, CreatedDate, ModifiedBy, ModifiedDate FROM Announcements a WHERE isActive = 1 AND CountryID = 2 AND (GETDATE()>= PostedDate) AND (GETDATE()<= ExpiredDate) ORDER BY PostedDate ASC
But result is displaying as below, PostedDate datatype is datetime
hi, How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003 1/13/2007 01132004 1-1-2004 1.2.2001
there is no uniformity of the data that is stored currently.
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
I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'
What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it.
is there any way I can order a varchar field numerically?
Hi: I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime. I use Query like: SELECT CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +CAST(SUBSTRING(date_sent,4,2) as int) + '/' +CAST(SUBSTRING(DATE_SENT,7,4) as int) From MyTable It is not working, anybody can give me some advise! thanks!
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
I'm trying to create a view using the following code:
quote:SELECT TOP (100) PERCENT Program, COUNT(Program) AS Total, (SELECT COUNT(Program) AS Count FROM dbo.Active_Enrollments_by_Earn WHERE (CDE_PROJ LIKE 'NC%') AND (Program = List.Program) GROUP BY Program) AS CDC, (SELECT COUNT(Program) AS Count FROM dbo.Active_Enrollments_by_Earn AS Active_Enrollments_by_Earn_1 WHERE (CDE_PROJ LIKE 'WS%0') AND (Program = List.Program) GROUP BY Program) AS WSC FROM dbo.Active_Enrollments_by_Earn AS List GROUP BY Program ORDER BY Program On my server, it sorts the resulting view in alphabetical order automatically (it didn't matter if I put "ORDER BY Program"). On my production server, however, it doesn't sort by Program at all and I can't seem to resolve it. HELP!
I am inserting rows into an Excel file and the ORDER BY is coming out wrong.
When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should. But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...
SET @sql = ' INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@finalFile+';HDR=YES'',
''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')
SELECT ID,priority,comments FROM OurTable WHERE orgId='+@orgId+' ORDER BY priority,ID'
I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.
I have the command ready which is:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
The problem happening is that it seems there are constraints across all the columns in every tables.
The error message is:
Server: Msg 5074, Level 16, State 1, Line 1 The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.
I have a textbox that is receiving a date in the format mm/dd/yyyy. The field in the database is VarChar size 10. I purposely don't want to use the DateTime data type for various reasons. When I extract the text from that textbox: Example:4/11/2005 I do my INSERT. When I look in the database at that field, it shows up like:April 11, 2005 12 The database's field is VarChar! How/why is it doing automatic DateTime formatting?
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code: Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID Where D.SchoolID = @DojoID and C.Days like'%' + @Days + '%' Union Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID Where D.SchoolID = @DojoID and @Date Between E.EventStartDate and E.EventEndDate
Hi,I want to change 2 default behaviors of ms-sql server within a connection.1. All varchar (etc.) fields should be returned as UTF-8.I know about field attribute collation and convert function,but I want simple query like:SELECT * FROM table;to return all varchars as UTF-8, no matter how the fields weredefined and which collation.In MySQL I execute: "SET NAMES 'utf-8';" after connecting to serverIn PgSQL I use native function: pg_set_client_encoding()2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"I just want all datetimes fields were returned as:"2008-04-20 12:01:02"I can do that with function convert() (style=20) but I want(as in question above) a simple query like:SELECT * FROM table;to return date and time formatted as described above.In MySQL and PgSQL the format "2008-04-20 12:01:02" is the defaultone.Is it possible to do that? If yes, please tell me how can I do that?Wladyslaw
Hi, I have a varchar field named FinancialYTDCode containing data in the format 2007F or 2008F. I want to only select the rows with the FinancialYTDCode that is the same as the current year.
Could someone please show me how I write this in my script. Thanks
Basically, I'm trying to take two dates and find the difference in hours b/t the two, sql server 2000. I've tried change the variables out from datetime to varchars even though it shouldn't matter. When I put the block of code out of the stored procedure and run it in line it works fine, I'M LOST.........
I get the errors everytime it runs into the first block, I get the error again when the block is hit again farther down in the procedure. I get:
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ERR dates Aug 4 2005 11:16PM. Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. ERR dates Aug 3 2005 12:35PM. Server: Msg 241, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 211 Syntax error converting datetime from character string.
IF @NextStatusDate IS NOT NULL AND @StatusDate IS NOT NULL BEGIN set @tmpTimeCharVar = DATEDIFF(SECOND, @NextSTatusDate, @StatusDate) set @tmpTimeVar = ( CASE WHEN @tmpTimeCharVar/3600<10 THEN '0' ELSE '' END + RTRIM(DATEDIFF(SECOND,@NextSTatusDate, @StatusDate)/3600) + ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) / 60),2) + ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) % 60),2) ) END IF @@ERROR <>0 PRINT 'ERR dates' + Convert(varchar(20), @NextStatusDate) + '.'
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics. So i tried: SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC? Patrick
So.. I'm trying to add up the number of wins and losses from a schedule of games and list them with their corresponding team name from another table.
Then I want to be able to sort by each teams number of wins. This is what I've got now, and it runs through without error, but it does not ORDER the list by "Wins"
strSQL = "Select *, ((SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Win = true) + " & _ "(SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Loss = true)) AS [Wins], " & _ "(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Loss = true) + " & _ "(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Win = true) AS [Losses], " & _ "(SELECT Count(Tie) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Tie = true OR Game_Schedule.T2_ID = standings.ID AND Tie = true) AS [Ties] FROM standings ORDER BY T_Tier, T_Name, Wins DESC"
