Correctly ORDER BY Date That Has Been Cast As A Varchar

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


ADVERTISEMENT

Cast A Varchar As A Date

Mar 5, 2007

I have developed a CF application and now need to add some charts to it for admin purposes. I have a form page that uses a calendar widget to let a manager select a date range and submits to display data for that timeframe. I need to convert the formfields to a date for my WHERE clause in my query. My experience to this point has just been simple queries of one table. below is a sample I found and modified numerous time to try and make it work with no success.

Can anyone give me a hand with this?

SELECT COUNT ((CHG_TYP_CD)) AS CHG_TYP,
CHG_TYP_CD
FROM WRK_RQS
WHERE(CAST('form.startdate' AS datetime) >= RQS_SBM_TS)
AND
(CAST('form.startdate' AS datetime) <= RQS_SBM_TS)
GROUP BYCHG_TYP_CD

View 4 Replies View Related

Cast Varchar Number Into Date Format?

Jul 12, 2013

I have a column (varchar (50)), It has values from 1-12.

I want to cast them to their appropriate months.

Example: 1 = January
2 = Febuary
3 = March
etc.

View 3 Replies View Related

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 View Related

Using CAST From Varchar To Int

Oct 15, 2006

I am pretty new to SQL for SQL Server 2005. In a view I have a column CAST(Field1 as int). Field1 is a varchar.All works well except when the data is not numeric such as a '?' or '*'.How can I get around this, with a case statement, coalese?I only want to perform the CAST on valid numeric values.The valid values in the varchar field are blank, null,'0','1','2','3','4','5','?','*' and maybe othersThanks

View 11 Replies View Related

Cast From Float To Varchar

Aug 1, 2006

Hi,

Can I convert from float to varchar without trunc the values? Can I use any mask like '#.##'?

from -> cast ( 123.44 as varchar(256) )

result = '123.44'

thanks,

View 1 Replies View Related

How To Cast A Varchar Column To DateTime

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

Cast Convert(varchar(12),newDate, 101)

Mar 25, 2008

1)Cast(isnull(Partl,0) as smallint)
2) convert(varchar(12),newDate, 101)

what two lines would do. ??

thanks,

View 1 Replies View Related

SQL 2012 :: Cast Varchar To Bigint Error

Mar 4, 2014

I try to run code but got an error. How to fix it?

SELECT Cast(('20140304800084500001') AS BIGINT)

Arithmetic overflow error converting expression to data type bigint

View 1 Replies View Related

Converting / Cast Varchar To Numeric With Scale?

Sep 1, 2013

I have a varchar(len=9) field that I want to cast as numeric(9,2). Some of the field values are null but some have valid values (ie 1.00 or .05).

I am selecting data from one table and then updating a different table.

I have a select statement within an insert statement and then an update statement. I've tried a cast statement but not successfully. I get the following error.

[Execute SQL Task] Error: Executing the query "Exec sp_ESTLoadPOData" failed with the following error: "Error converting data type varchar to numeric.". Possible failure reasons:

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

ORDER BY CAST(classname As Int)

Jul 24, 2001

When I do the following:

SELECT Name, Age FROM Students ORDER BY CAST(classname as int)

I'll get an error saying that "can't convert '1b' to int" and I am completely ok with that. However, I would still like to ORDER BY the int
value in the column [classname] that is of type varchar(5) so that i would get the ordering numeric. I don't want '22b' to come before '3b' !

Thanks for any help !

View 4 Replies View Related

Error When Convert Or Cast Functions From Varchar To XML Datatype

Dec 29, 2007

Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems.
When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below 
I am trying to do convert(xml,MyVarcharColumn)
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Also i tried with casting and getting same problme. is there any way to convert
 
please suggest me
 
Thanks
Dilip

View 1 Replies View Related

Order By Case Cast Convert Error

Jul 23, 2005

I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End

View 11 Replies View Related

T-SQL (SS2K8) :: Adding N Prefix In Update Statement In Order To Store The Text Correctly?

Sep 4, 2014

The FirstName and LastName values being passed in are in cyrillic text.

How do I add the N prefix in the update statement in order to store the text correctly.

I've tried FirstName = N@Firstname

or FirstName = '''N''' + @FirstName + '''

[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),

[code].....

View 8 Replies View Related

Urgent !! Date Is Not Stored Correctly

Jan 15, 2008

Hello all ..
I have a form that views the date automatically in a textbox... the date of this text box must be stored in the database after the user press the submit button ... However, I am getting a wrong date which is 01/01/1900 .. although I see it in the form as 15/01/2008 ..
this is the code ... please give me a solution ASAP ..
 
 Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
'Declaring the variablesDim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim courseId, traineeName, traineeId, department, comment1, comment2 As String
Dim formDate As Date
Dim RB1, RB2, RB3, RB4, RB5, RB6, RB7, RB8, RB9, RB10, RB11, RB12, RB13, RB14 As String
Dim ConnectionString, evaluationSQL, TraSQL, iTotal As String
'Save form values in variables
formDate = Convert.ToDateTime(DateTxt.Text)
courseId = CourseIDTxt.Text
traineeId = EmailTxt.Text
traineeName = TraineeNTxt.Text
department = DeptDropDownList.SelectedValue
RB1 = RBL1.SelectedValue
RB2 = RBL2.SelectedValue
RB3 = RBL3.SelectedValue
RB4 = RBL4.SelectedValue
RB5 = RBL5.SelectedValue
RB6 = RBL6.SelectedValue
RB7 = RBL7.SelectedValue
RB8 = RBL8.SelectedValue
RB9 = RBL9.SelectedValue
RB10 = RBL10.SelectedValue
RB11 = RBL11.SelectedValue
RB12 = RBL12.SelectedValue
RB13 = RBL13.SelectedValue
RB14 = RBL14.SelectedValue
comment1 = CommentTxt1.Text
comment2 = CommentTxt2.TextConnectionString = WebConfigurationManager.ConnectionStrings("GRPConnectionString").ConnectionString
'Create and open the connection objConnection = New SqlConnection(ConnectionString)
objConnection.Open()evaluationSQL = "Insert into Evaluation (Eva_Date, CourseD_Id, Tra_Id , Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Comment1, Comment2 ) " & _
"values(" & formDate & ",'" & courseId & "','" & traineeId & "','" & RB1 & "','" & RB2 & "','" & RB3 & "','" & RB4 & "','" & RB5 & "','" & RB6 & "','" & RB7 & "','" & RB8 & "','" & RB9 & "','" & RB10 & "','" & RB11 & "','" & RB12 & "','" & RB13 & "','" & RB14 & "','" & comment1 & "','" & comment2 & "')"objDataCommand = New SqlCommand(evaluationSQL, objConnection)
objDataCommand.ExecuteNonQuery()

View 3 Replies View Related

Getting JUST Date From Datetime Not Displaying Correctly

Jun 2, 2008

here is my select command:

SelectCommand="SELECT [JobID], [EmployeeName],

CAST(

STR(YEAR(DATEENTERED)) + '/' +
STR(MONTH(DATEENTERED)) + '/' +
STR(DAY(DATEENTERED)) AS DATETIME
) AS Date,

[From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs]"

It will show correctly in the query builder test but when you load it up in the gridview it will show the date AND 12:00:00AM for every one of the dates

I tried replacing DATETIME with VARCHAR but it would insert spaces into the date where I didn't want them: EX: (12/04/1994 => 12/ 04/ 1994)

any suggestions?

View 1 Replies View Related

How Do I Use A Date Parameter Correctly In A SP To Be Used With SSRS

Mar 24, 2008



I am having trouble adding a smalldatetime parameter @ScrubbedDate to my procedure and getting it to work with my report.

I get the cannot convert string to smalldatetime error.

View 6 Replies View Related

Converting Date To Varchar? And Varchar To Date?

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

DATE Cast

Oct 19, 2006

Hello Everyone,

How do I change the format of the Date:

2006-06-01 00:00:00.000

to this:

06-01-2006.

I believe this can be done with a Cast.

Am I correct?

TIA


Kurt

View 6 Replies View Related

How To Order By Varchar

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

Find Order By Date Range Or Order Id

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

Cast From String To Date

Sep 21, 2006

I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.

Now in my SELECT statement, I have this:

SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @startdate) AND (TLMST.INDT <= @enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER

Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.

Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information

View 5 Replies View Related

ORDER BY &&<VarChar Field&&>

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

Transact SQL :: Cast Zero AS DATETIME2 Or DATE

Jul 22, 2015

Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01.  However, you can't cast 0 to DATE or DATETIME2.  Why is that?

View 15 Replies View Related

HOw To Order Fields Of Type Varchar?

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

Select As VarChar But Order By DateTime

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

Reporting Services :: Date Format MM/DD/YYYY Not Sorting Correctly

Aug 17, 2015

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?

View 11 Replies View Related

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007



I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'


As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

Cast Question - Converting Datetime Into Date

Sep 21, 2005

Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.

I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004

Hope you can help me up. Thanks.
-vince

View 3 Replies View Related

Transact SQL :: Using CAST To Change Timestamp Into Date?

Nov 6, 2015

I have a server on SQL Server 2008 R2.

I have the following code:

SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROM PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID

However when I run it I get a message:

View 7 Replies View Related

Date Conversion Woes: Cast? Convert?

Nov 7, 2007

I have been having some trouble trying to get the date format YYYYMMDD 00:00:00.00 to convert to MM/DD/YYYY. I am using SQL 2005 and Reporting Services. I attempted several variations to solve this problem as outlined below, with the final working conclusion at the end. I am not saying that this is the BEST or the ONLY way to achieve the solution, but it worked for me.



Code Block
---------------------------------------------------------------------------------------
SELECT DATE_TIME AS DATE1
FROM TRANSLOG
--returns value of 20071027 02:26:24.06
---------------------------------------------------------------------------------------
SELECT CAST(DATE_TIME AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 02:26:00
---------------------------------------------------------------------------------------
SELECT LEFT(CAST(DATE_TIME AS SMALLDATETIME),11) AS DATE1
FROM TRANSLOG
--returns value of Oct 27 2007
---------------------------------------------------------------------------------------
SELECT CAST(SUBSTRING(DATE_TIME,0,9) AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 00:00:00
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), DATE_TIME, 103)
FROM TRANSLOG
--although this works with GETDATE(), it does not work with the DATE_TIME field
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), CAST(DATE_TIME AS SMALLDATETIME), 101) AS DATE1
FROM TRANSLOG
--THIS WORKS!!! Displaying 10/27/2007





Hope this helps somebody someday!

-Jody

View 5 Replies View Related

Conditional Statement With A Cast From String To Date

Feb 5, 2007

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

View 9 Replies View Related

Integration Services :: Date Timestamp Not Loading Correctly Into CSV File As Destination

Nov 5, 2015

I have a simple package to load data from sql server db into a flat file. I have a date field in the source data base (data type DATETIME) when i open the csv file some show the exact time stamp and some records show just the seconds like (00:00:0.7). I used CAST CONVERT bu still the same issue.

AppliedDate
00:00.6
00:00.6
10/2/2015 0:00
10/2/2015 0:00
00:00.3
00:00.3

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved