ORDER BY [Date] Isn't Working Since It's Not A DateTime (it's A Varchar!)
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
ADVERTISEMENT
Mar 13, 2008
Hello,
I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION.
In other words, does anyone know how to make this work:
create table #temp1 (
d datetime
)
create table #temp2 (
d datetime
)
insert into #temp1(d)
select '2001-12-12' union
select '2002-11-11'
insert into #temp2(d)
select '2003-10-10' union
select '2004-09-09'
--works fine-------------------------------
select convert(varchar, d, 101) dd from #temp1
order by d
-------------------------------------------
--but can't make this work-------------------
select convert(varchar, d, 101) dd from #temp1
union select convert(varchar, d, 101) dd from #temp2
order by dd -- wrong type of sorting
--order by d-- error
--order by cast(dd as datetime) -- error
-------------------------------------------
drop table #temp1
drop table #temp2
I would really appreciate your help.
Thanks in advance,
Thomas
View 5 Replies
View Related
May 23, 2008
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,
View 4 Replies
View Related
Oct 3, 2006
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.
View 7 Replies
View Related
Jan 16, 2015
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
01/01/2015
01/02/2015
12/28/2014
12/31/2014
Expected result is
01/02/2015
01/01/2015
12/31/2014
12/28/2014
View 1 Replies
View Related
Sep 11, 2007
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.
thnkx in adv.
rahul jha
View 11 Replies
View Related
Sep 21, 2006
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
View 3 Replies
View Related
Jul 31, 2007
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
View 1 Replies
View Related
Dec 30, 2005
Dear all,
I have a varchar field which have data:
ID_trans
---------
T1
T10
T11
T2
T3
everytime i use select statement with order by, the result is above
Does somebody know..? HOw to make the result like below
ID_trans
---------
T1
T2
T3
T10
T11
Sorry i'm not good in english...
Thanks
View 10 Replies
View Related
May 8, 2007
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
View 4 Replies
View Related
May 23, 2006
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
View 1 Replies
View Related
Mar 11, 2014
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
View 1 Replies
View Related
Mar 22, 2000
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?
Angel
View 1 Replies
View Related
Apr 20, 2005
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!
View 4 Replies
View Related
Sep 17, 2007
Hi,
I wanted to convert the varchar to date time and here is what i am doing
DECLARE @dt VARCHAR(20)
SET @dt = '20070111' -- YYYYMMDD format
select CONVERT(datetime, @dt, 120)
This works perfectly fine and the result would be- 2007-01-11 00:00:00.000
But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing
"Conversion failed when converting datetime from character string."
Can any one please let me know how do we achieve this?
~Mohan
View 3 Replies
View Related
Oct 23, 2007
Hi,
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.
Thanks & Regards,
Frens
View 7 Replies
View Related
Apr 23, 2008
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!
View 11 Replies
View Related
Aug 29, 2007
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'
EXECUTE (@sql)
Please help!! Thank you!
View 1 Replies
View Related
Nov 14, 2007
Hello,
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.
View 1 Replies
View Related
Apr 12, 2005
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?
View 3 Replies
View Related
Aug 8, 2006
hi, good day, if i have a table as follow:
Create table test
(
student_name varchar(20),
student id varchar(20),
register_dt varchar(50)
)
and insert data like
insert into test values('rebecca','0001','2006-08-08 12:15:03')
after all , i would like to query the data and insert into another table say data_tbl where it define as follow
Create table data_tbl
(
student_name varchar(20),
student id varchar(20),
register_dt datetime
)
would the test table regiter_dt auto convert into datetime format or we need to convert it before insert into data_tbl table?
View 3 Replies
View Related
Jan 21, 2012
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
how can i cast it correctly?
View 1 Replies
View Related
Oct 2, 2014
I can't get a varchar to datetime conversion to work. The varchar data is in the form yyyymmddhhmi
I tried convert(datetime, '200508310926')
also tried cast('200508310926' as datetime) both have error "Conversion failed when converting date and/or time from character string"
Is there a format code that needs to be there? If so, I can't figure out what it should be.
I eventually need to convert these to dates and compare them to getdate, example:
...where convert(datetime, dtfield) >= getdate()-1
View 7 Replies
View Related
May 27, 2008
Hi All,
I have a table called Table1 with a field Date (varchar type). The records are like this;
05/21/08
07/02/08
06/04/08
06/10/08
I want to convert the above into datetime (yyyy-mm-dd format)....in the above example my output should be;
2008-05-21
2008-07-02
2008-06-04
2008-06-10
How can i do this?
Please help.
Zee...
View 2 Replies
View Related
Jun 17, 2008
Hello,
I have this column of time values:
00:00:03.3592675
00:00:00
00:00:03.8592515
00:00:03.6873820
00:00:03.8436270
00:00:03.3436430
It is in varchar format. I tried converting it to datetime but get an invalid format error message.
How can I sum up these values and average them out?
Thanks!
--PhB
View 1 Replies
View Related
Jun 19, 2014
I have a column on my table with the varchar(50) datatype. The whole column has the value 2014-04-31
I want to Convert the varchar(50)datatype to datetime datatype.
The table name is called - dbo.pracs
the column name is - LastDatUpaded
View 6 Replies
View Related
Dec 14, 2007
I have this procedure and it can't convert this date to datetime type:
Exec scnr.prc_tblScannersReemplazarClasificacion
'CERVEZA ',
'CERVEZA',
'ENV VAC ',
'ENVASE VACIO',
'NULL',
'NO aplica',
'NULL',
'NO aplica',
'1-8-2007',
'10-12-2007',
'ISCRacajina';
and after that i tried to execute it, but I changed the date like next:
Exec scnr.prc_tblScannersReemplazarClasificacion
'CERVEZA ',
'CERVEZA',
'ENV VAC ',
'ENVASE VACIO',
'NULL',
'NO aplica',
'NULL',
'NO aplica',
'8-1-2007',
'12-10-2007',
'ISCRacajina';
and the procedure reseive this parameter:
CREATE PROCEDURE scnr.prc_tblScannersReemplazarClasificacion(@tipoclasificacion varchar(200),
@categoriaV varchar(200),
@categoriaN varchar(200),
@subcategoriaV varchar(200),
@subcategoriaN varchar(200),
@tipoV varchar(200),
@tipoN varchar(200),
@subtipoV varchar(200),
@subtipoN varchar(200),
@fechascanner datetime,
@fechaingreso datetime,
@usuarioscanner varchar(200) )
The error is:
Msg 8114, Level 16, State 5, Procedure prc_tblScannersReemplazarClasificacion, Line 0
Error converting data type varchar to datetime.
Why can't this procedure reseive these dates?
What can I do?
Help me...
View 3 Replies
View Related
Apr 10, 2008
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
View 3 Replies
View Related
Feb 22, 2008
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
View 9 Replies
View Related
Aug 22, 2007
declare @a varchar(10)
select @a= shiftstarttime from o_parameter
print @a
declare @b varchar(10)
select @b= shiftendtime from o_parameter
print @b
declare @dt varchar(20)
set @dt=Left(getdate(),12)
print @dt
declare @dt1 varchar(20)
set @dt1=Left(dateadd(dd, 1,getdate()),12)
print @dt1
declare @dt3 varchar(20)
set @dt3= @dt1 + space(0) + @a
print @dt3
declare @dt4 varchar(20)
set @dt4= @dt + space(0) + @b
print @dt4
output of above script is as fallow
09.30am
06.30pm
Aug 22 2007
Aug 23 2007
Aug 23 2007 09.30am
Aug 22 2007 06.30pm
now i want to convert @dt3 and @dt4 into datetime .
because i wnat to calculate datedifference in hours by using this function
SET @in_hour=DATEDIFF (HH ,@D1,@D2)
where @D1 and @D2 are datetime parameters.
but how can i get @dt3 and @dt4 into datetime so i can pass it to DATEDIFF() function.
so plz Guide me. or if u know how to write it then plz write here
i already use cast for it but it doesn't work.
so plz reply urgently.
View 2 Replies
View Related
Jan 30, 2008
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.
Code:
Declare
,@CodePair varchar(10
,@CodePairTimeDiff dec ,
@tmpTimeVar datetime ,
@tmpTimeCharVar varchar(30)
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) + '.'
inside update statement
,CodePairTimeDiff = CONVERT(dec, (100 * DATEPART(HOUR, @tmpTimeVar)
+ DATEPART(MINUTE, @tmpTimeVar))) / 60
View 12 Replies
View Related
Jan 17, 2007
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
View 1 Replies
View Related
Dec 11, 2007
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"
View 2 Replies
View Related
