SQL Server dates but text value

Hi

Am trying to filter on dates in a query in SQL Server...data has dates in text and my sql dont appear to be working

ie

Code:

---

SELECT [Date signed] FROM tblContactsCC WHERE ([Date signed] < CAST(N'01/10/2008' AS datetime))

SELECT [Date signed] FROM tblContactsCC WHERE [Date signed] <= convert(datetime, '01/10/2008',103)

---

What am I doing wrong.....

So is 'Date signed' a Date column or some sort of Text column ?

this 1 is an nvarchar.....I have another table....its datetiem works perfect using routine above.

Can I filter on text values??

If it is in NVarchar format you can't cast (or convert) the incoming filter to a date type it must stay string. This will cause issues with the results of course. You could use a Convert on the field and Cast (or Convert) that to a Date type but if the table is large you might have issues as the query will not use any index on the column. You could create an index again casting (or converting) the field to the Date data type and use a hint in the query to use that index if required.

yes thats good idea...not my routines so not sure if I can modify import routines and field to timedate....thanks for help

Not saying to modify the field to datetime but cast it to datetime in the query

SELECT [Date signed] FROM tblContactsCC WHERE convert(datetime, [Date signed] , 103) <= convert(datetime, '01/10/2008',103)

yeah i got that, was just saying ideally would have liked to have fixed it by getting value in datetime

I see what you mean about query...I was going to create a new field but your query saves me doing that.....

OK ran it on the table its getting errors.... have filtered out nulls and 0's but its now saying it doesnt like a date "Conversion failed when converting the nvarchar value '28/03/2008' to data type int." User data you gotta love it!!

So the date value is in DD/MM/YYYY you will need to set the conversion to that type. Hopefully the data is all done the same way

I dont think its is....... i suspect I might have to make a field up and populate it with values that can be converted...unless theres a way to error trap the offending strings that are non date

If the data is not consistant then I think you are in trouble. Maybe your only choice will be to add a field and convert into there using an SP to start then Use a trigger to fire after insert to continue poulating over time.

How would I code a trigger? and what it it hits error?

Never done a trigger before.....

A trigger is very similar to a stored procedure. You can code Try Catch blocks in SQL Server 2005 and up.

If it errors on on version of the date type you try the other. But that still leaves a lot of holes.

7/12/2010 this will convert both ways July 12th 2010 or Dec 7, 2010 which is it? How can you tell? Is there some thing in the row that shows where the data came from you could possible use that to decide which way the data should be converted.

ok i think i will add extra field and populate..... and on next import check for new dates and change where possible.... if it cant be converted flag in report to user.

its really upto the user to check and make sure the data in the table is right ie not mixed up date with dd mm

Quote:

---

If it is in NVarchar format you can't cast (or convert) the incoming filter to a date type it must stay string.

---

Yep that's why i was asking, but it looks as though Gary has this all wrapped up :0)

am still getting problems when I try to update the new date values into the new field

How would I write a function to do igonre the problems...I wrote this in vba

vb Code:

If IsNull(myDate) Or myDate = "" Or Len(myDate) < 8 Then
        'ConvertDate = Null
    Else
        ConvertDate = CDate(myDate)
    End If

this is what I have so far, but need to throw out the 0 and nulls

vb Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertDate](@InDateTime nvarchar)
RETURNS datetime
AS
BEGIN
    DECLARE @MyOutput datetime
    SET @MyOutput = CONVERT(datetime,@InDateTime,103)
    RETURN @MyOutput
END

This is where I got to, giving me errors...doesnt like the IF and isnull??

vb Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertDate](@InDateTime nvarchar)
RETURNS datetime
AS
BEGIN
    DECLARE @MyOutput datetime
 
    if isnull(@InDateTime) or Len(@InDateTime)< 8 then
            --SET @MyOutput = ""
            RETURN @MyOutput
    else
            SET @MyOutput = CONVERT(datetime,@InDateTime,103)
            RETURN @MyOutput
    end if
END

I accept with code: 1.
set ANSI_NULLS ON
2.
set QUOTED_IDENTIFIER ON
3.
GO
4.
ALTER FUNCTION [dbo].[ConvertDate](@InDateTime nvarchar)
5.
RETURNS datetime
6.
AS
7.
BEGIN
8.
DECLARE @MyOutput datetime
9.
SET @MyOutput = CONVERT(datetime,@InDateTime,103)
10.
RETURN @MyOutput
11.
END


__________________
Watch The Wolfman Online Free

How about this -

Quote:

---

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertDate](@InDateTime nvarchar)
RETURNS datetime
AS
BEGIN
DECLARE @MyOutput datetime

if @InDateTime = null or Len(@InDateTime)< 8
begin
RETURN @MyOutput
end
else
begin
SET @MyOutput = CONVERT(datetime,@InDateTime,103)
end
RETURN @MyOutput
END

---

obviously you still need to finish it off !

thanks guys, I tried this morning needsomeanswers (its weird calling you that name!!) wee changes to my routine...and hey presto its now working!!

I might go back and see if I can hook this up to Garys select SQL

many thanks for your help guys

If it won't be a big effort to modify the client codes, I would suggest you convert it to a date field, instead of nvarchar. A date should be stored in date field and this is a perfect example of what happens if you don't.
If there's a lot of effort involved or otherwise not possible due to any other reason, I'd suggest you add a calculated field to your table and work with that. Calculated fields are populated automatically, so no trigger etc. would be required (unless your data is really badly messed up).

I am adapting a "user created mess" of access database and excel pivot tables into my lovely nicely designed database.... so at first I normally I take whats there change what I can..... of course sometimes time and users stop this happening 100&#37; but once its mine I can move it towards a cleaner normal database app.

No problem,

remember in TSql you need to have Begin / End tags around your If conditions and the ISNULL() function is for use in-line in a select statement so you can replace Nulls with another character(s) e.g

SELECT name, age, ISNULL(height,'N/A') from person where id = 1

Oh and my name is Rich by the way :0)

ok this appeared simple but its not outputing the datetime value in Update or make table query.... am confused

vb Code:

UPDATE    tblContactsCC SET  DateSigned = dbo.ConvertDate([Date signed])

Just getting nulls....field DateSigned is datetime output from function is @MyOutput datetime

Any idea why its not liking it?

thanks Rich for help, Tsql is not 1 of my strengths

before you try an Update why don't you try a Select statement. That should show you what you are getting out of Convert Date

something like this -

SELECT myDate, dbo.ConvertDate(MyDate) from table

This way you get your initial Date, and also your converted date. This should give you an idea of why it is not working.