Date Sorting In Varchar

Jul 20, 2012

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.

View 6 Replies

Sorting Numbers Stored As Varchar

Mar 19, 2008

Hi All,

I have a field in a table that is varchar. The field can contain numbers or alpha characters. I have a view against this table and I am trying to sort on the output of this field and am running into some problems.

I am trying to use 'isnumeric' to determine if the field contains numbers and if so, then I am using 'convert' to change it to an integer so I can sort it properly. I am using a CASE statement but I am encountering a conversion error on the alpha characters. I am new to SQL and my syntax may be wrong.

Can anyone help please?

SELECT TOP 100 PERCENT SpaceName, CASE WHEN isnumeric(RoomTable.RoomNumber) <> 0 THEN CONVERT(int, RoomTable.RoomNumber)
ELSE RoomTable.RoomNumber END RoomNumber
FROM dbo.RoomTable
ORDER BY SpaceName, RoomNumber

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

Sorting By Date And Then Sorting By Name (within Date)

Jan 7, 2008

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)

Thanks for any advice!

View 2 Replies View Related

Sorting By Date

Apr 13, 2008

I have a table with a date column. The data looks like this:

11/22/2001 12:00:00 AM

I need to sort the data by the date in ascending order by year, then month, then day.

In my SQL query, I have:

ORDER BY Date ASC

But that's not working. Is there another way?

View 9 Replies View Related

Sorting Date

Nov 17, 2006

saras writes "In sql server ,How to Sort a date which is converted into varchar.I need a single column"

View 13 Replies View Related

Sorting Of Date Fields

Dec 27, 2004

hi,

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.

View 6 Replies View Related

Next Run Date Column Not Sorting

Jul 20, 2005

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!

View 4 Replies View Related

Sorting Date/timestamp Is Out Of Order

Jan 18, 2005

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?

View 3 Replies View Related

Sorting By Today's Date And Greater

Oct 30, 2013

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

order by SSD_SED >= GETDATE()

Where the date field is SSD_SED.

View 10 Replies View Related

Sorting By Date,time Then Select

Sep 24, 2007

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?

View 3 Replies View Related

Reporting Services :: Sorting Numbers By Date

Jun 17, 2015

I have the attached picture in my SQL Report Builder 3.0 report. How do I combine the months that show more than once into a single column?

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

Reporting Services :: Tablix - Sorting Last Column Which Is A Dynamic Date From 0 To 100?

Aug 7, 2015

I have a tablix that looks like this...

30 Jul
31 Jul
01 Aug
02 Aug
03 Aug
04 Aug
05 Aug

Region1
0.00
0.00
0.00
0.00
100.00
100.00
0.00

[code]....

I would like to sort the last column which is a dynamic date (always the most recent from the last 7 days) from 0 to 100.

View 11 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

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.

Any suggestions would help.

Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Varchar To Date

Jan 13, 2004

This is probably a very simple question, but there it goes.

I have a DB table with the following varchar value:
1/12/2004

I want to convert the specified data to the following:
Monday, January 12th, 2004

I guess i want to:
Convert(DATETIME, '1/12/2004'), But...
I want it to be inserted into the new table as a long date type value.

Is this possible?

Thanks in advance!

View 5 Replies View Related

Need Help In Sql(Varchar To Date Conversion)

Jan 10, 2002

Hi:
here i am giving one example with Emp table and fields ssn,empjoindate

I have a table with field empjoindate as varchar(10).I need number of employees joined between 12/23/01 and 01/04/02.I ran this query using select ssn from EMP where convert(varchar(10),empjoindate,101) between '12/23/01' and '01/04/02'.it is not fetching any data.

Please help me
Thanks
Tej

View 8 Replies View Related

Compare Varchar Date

Jan 10, 2006

I have a preexisting database that has dates in the format mm/dd/yyyy but it is set up as varcar. How can I apply numeric operators to it to find how old someone is from todays date? I then need to take that value and populate another column on the same row.

View 1 Replies View Related

Convert Date To Varchar

May 27, 2008

I have a datetime column and I would like to update another column in the same table with the varchar value of date. e.g. date column has 06-08-2008 as a value. I would like to have the same value in the varchar field.

wondering which function can I use to accomplish this task?

thanks

View 4 Replies View Related

Varchar To Date Conversion

May 30, 2008

I have Field which contains Date in dd/mm/yy fromat. I need to convert it into DateTime format so that it can be processed.

Please help

View 2 Replies View Related

Dynamic SQL - Date To Varchar

Jun 18, 2008

I cannot execute a dynamic SQL function when I convert a datetime to a string:

declare @date_key datetime
set @date_key = '5/1/08'

select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''' + convert(varchar(20), @date_key, 101) +'''')

The code below produces the exact same text but DOES work:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalc ''05/01/2008''')

View 6 Replies View Related

Convert Varchar To Date

Nov 18, 2005

Hi

I have a varchar field with values like "Jun 13 1995 12:" I want to change the type of the field to Date and the format of the values to "mm/dd/yyyy".

If I change the type of the field in the design view to Date, SQL gives me an error saying that the type cannot be changed and data would be lost.

So I think changing the dates to the mm/dd/yyyy format first and then changing the field type from varchar to date would work.

Can someone please help me as how to do this.

Thanks

View 6 Replies View Related

Date Time Varchar

Aug 2, 2006

Hi All,

We are trying to create a new view within SQL 2000. We have two colums from seperate tables, 1 is a datetime data type which stores the Date the other is a varchar that stores the time.

What we would like to do is merge the two into one datetime colum so we can work with this one datetime column. Unfortunaly it is not posible to have this entered into the one field at data entry. If needed we can change the time column from a varchar to a datetime datatype.

Also our SQL knowledge is minimal.

Hope this makes sense any help would be deeply appreciated.

Regards

Darren

View 9 Replies View Related

Varchar Date Problem

Jan 28, 2007

have a field called sigdate datatype varchar that saves dates

like these

01/2/2007
12/2/2007
06/19/2007
01/21/2007

i want to show distinct months/year in a drop down

so the query should output

01/2007
12/2/2007
06/2007

here is my code

SELECT Distinct(convert(varchar(2),month([sigdate]))+convert(varchar(4),year([sigdate]))) as [monthyear] from stdreg

but its not working

any ideas

todd

View 4 Replies View Related

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

Converting Varchar Into Date

Aug 14, 2007

Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg

Before the date was
5/22/2000

UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)

Now it is this

Dec 23 200

Please tell me what to do , should i use cast or convert ?

Thanks in Adv

View 7 Replies View Related

Converting Date To Varchar

Jan 22, 2008

Hi all I would like anyone to help I want 2 convert a datetime to 2008-02-02 21:00 the output must leave the time just 2008-02-02

If (@DateCreated <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And convert(varchar(10),DateCreated,121) = '+ cast(@DateCreated as varchar(10))
End

View 4 Replies View Related

Convert From Varchar To Date

Feb 3, 2008

Hi,
Can any one help me in that case?
I have 2 colomns one have the month (1,2,3,4,....,12) and the other have the years (2007,2008,....)
I want to merge both in one date field MM/DD/YYYY and the day will always be '01'
Thank you
M.Maraghy

View 2 Replies View Related

Converting Varchar To Date From Asp.net To Sort

Jul 20, 2004

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?

View 5 Replies View Related

Change Of A Varchar To To A Date Field

Feb 28, 2001

Hi,

I have to change a due_date field with a varchar(10) to a datetime. I created a new table with datetime but when I imported data into the table it errored out due to the datatype. A varchar(10) going into a datetime(8).

Is there way to resolve this without losing data?. or how do you result this problem?.

Thanks for your help,
Jeff.A.

View 1 Replies View Related

Transact SQL :: Convert Varchar To Date?

Nov 27, 2015

Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are

11122015
11122015
11122015

Nothing has worked so far.

View 3 Replies View Related

Convert Varchar To Date In Where Condition

Mar 31, 2008

Hi guys.
i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..

select * from table
where cast(Field as datetime) between @startdate and @enddate

second query that i m trying to exectue is

select * from table
where cast(Field as bigint) = @Fieldid

but both are not returning any value...can anyone tells me where i m doing mistake

View 21 Replies View Related