Running a LIKE statement when searching for a date field...

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

Where did you find that preferred way to compare dates is LIKE. You might want to use many date functions to compare dates available in 2k and 2k5.

OR if you incist on using LIKE convert "wheninstalled" to format that you specify in LIKE.

|||datetime is stored in database in an internal format. It is not in YYYY-MM-DD or whatsoever format.

>> I have dates in this column ie 2006-02-13 11:30:54.220
This just how Query Analyser represent the date or format the date and time when it return the records.

>> SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
To retireve records for month of Feb 2006,

SELECT * FROM workorderhistory WHERE wheninstalled >= '2006-02-01' and wheninstalled < '2006-03-01'

|||

Here is the text from the sql help file.

It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

Thanks for your help.

|||Thanks for the help... I don't konw why I did not think of doing it that way... Slipped my mind I guess.|||

try this may be helpful for you

SELECT * FROM
WHERE (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) = '3/14/2006')

|||

How can I use a like statement in there. I tried doing

select * from workorderhistory where (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) like '03/%') and this did not work. Basically I want to see all the workorders that were installed in the month of march.

Running a LIKE statement when searching for a date field...

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

Where did you find that preferred way to compare dates is LIKE. You might want to use many date functions to compare dates available in 2k and 2k5.

OR if you incist on using LIKE convert "wheninstalled" to format that you specify in LIKE.

|||datetime is stored in database in an internal format. It is not in YYYY-MM-DD or whatsoever format.

>> I have dates in this column ie 2006-02-13 11:30:54.220
This just how Query Analyser represent the date or format the date and time when it return the records.

>> SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
To retireve records for month of Feb 2006,

SELECT * FROM workorderhistory WHERE wheninstalled >= '2006-02-01' and wheninstalled < '2006-03-01'

|||

Here is the text from the sql help file.

It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.

Thanks for your help.

|||Thanks for the help... I don't konw why I did not think of doing it that way... Slipped my mind I guess.|||

try this may be helpful for you

SELECT * FROM
WHERE (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) = '3/14/2006')

|||

How can I use a like statement in there. I tried doing

select * from workorderhistory where (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) like '03/%') and this did not work. Basically I want to see all the workorders that were installed in the month of march.

Run time error: out-of-range datetime value

Hi everyone, this is the exception:

System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Here's my code!

... 'datagrid code
cmdSelect.CommandText = "SELECT * FROM [Table] WHERE [Date and Time] > '" & Me.tbFromDate.Text & "' AND [Date and Time] < '" & Me.tbToDate.Text & "'"
...

Me.tbFromDate.Text and Me.tbFromDate.text look like this: 27/11/2003 1:11:43 PM

The SQL Query works fine - just that this code gives a runtime error.

Can someone help?? I'm guessing I have to do somekind of conversion!!

AndrewDoes your sql query work fine with 27/11/2003 1:11:43 PM
or 11/27/2003 1:11:43 PM

While checking the date Try DateTime.Parse<your date>)

Run a SP with a datetime interval from a table

Hello,

I have something i called a deletelist, inside this list is items that is waiting to be deleted.

The deletelist looks like this:
username nvarchar(20),
itemtype int,
deletedate datetime

The SP that runs once everyday and deletes post is calledData_DeleteFromDeleteList.

Inside of this SP i have afew delete functions like this one:
DELETEFROM reg_userWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 0)))

Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:

SELECTEXEC [dbo].[Data_CompletlyDeleteAUser] @.UserName= username, @.QResult= @.QResultOUTPUTFROM data_deletelistWHERE(username=(SELECT usernameFROM data_deletelistWHERE(deletedate<getdate()AND itemtype= 2)))

any idea of how i can rewrite this function so it works??
in this SP im not using the @.QResult for anything, thats in another SP

At the end of this SP, i just delete all the old records from the deletelist like this,

DELETEFROM data_deletelistWHERE(deletedate<getdate())

I don't think there's a way to do something like this without a cursor, and those should be avoided if possible. You can do a couple of things here

1.) maybe you can change the proc Data_CompletelyDeleteAUser to be Data_CompletelyDeleteAllUsersWithItemType and pass it 2, then in your proc, you would do whatever it does for ONE user, but for all of them that match that type from the deletelist table.

2.) you can get rid of the proc altogether (if nothing else is using it) and jsut embed the logic into the main SP, which will allow for the operation to be set based.

You can use the cursor if this proc will run once nightly and there aren't too many records, but if 1 or 2 works for you, I'd go with those above all else.

Once 2008 comes out we'll be able to pass Tabular data to stored procedures so this won't be an issue anymore.

--D

|||

The problem with combining the procedures is that, the Data_CompletelyDeleteAUser contains over 1000 lines of code =/