wildcard date time format
-
I am new to this but here goes:
My query:
SELECT X.CreatedWhen, X.OrderStatusCode, X.ReasonText, X.Active,
Y.BirthDayNum, Y.BirthMonthNum, Y.BirthYearNum, Y.DisplayName, Y.IDCode, Y.ApplicSource
FROM X INNER JOIN
Y ON X.ClientGUID = Y.GUID
WHERE (X.OrderStatusCode = 'PERF') AND (X.CreatedWhen LIKE (CONVERT(datetime('2/8/ 2007 ')))
Basically, I am trying to search a huge database by date time. This datetime is a string so I want to convert to a true date time. I am in Sql 2000 and I keep getting an error:
ODBC SQL Server Driver Sql Server Line1: Incorrect syntax near '2/8/2007%'.
Any help would be greatly appreciated.
-
Problem is your convert statement, should be:
CONVERT(datetime, '2/8/2007')
If you are using a datetime in string format, you really should code it this way: 'yyyy-mm-dd'
This will work regardless of the dateformat setting for the server.
-
To add onto what Lynn said....what date is 2/8/2007? Is it Febuary 8, 2007 or 2 August 2007? How is SQL Server supposed to know?
Either use the yyyymmdd format or start your script with SET DATEFORMAT DMY or SET DATEFORMAT MDY depending on which format you are using.
Also, % is not a datetime value. It is a string value and you can only use LIKE with strings.
-SQLBill
-
You could try:
(CONVERT(VARCHAR(10),X.CreatedWhen,101) LIKE '2/8/2007'))
if the date is mm/dd/yyyy
or
(CONVERT(VARCHAR(10),X.CreatedWhen,103) LIKE '2/8/2007'))
if the date is dd/mm/yyyy
but personally, I would do:
(CONVERT(VARCHAR(10),X.CreatedWhen,112) LIKE '20070208'))
-SQLBill
-
Thank you very much. I used the (CONVERT(VARCHAR(10),X.CreatedWhen,112) LIKE '20070208')) and it worked like a charm.
Thanks for being kind to a novice. You guys rock!
-
It is not true that YYYY-MM-DD will work with any dateformat setting, as the following code demonstrates. The only universal format is YYYYMMDD.
set dateformat ydm print 'Convert 20071230' select Date=convert(datetime,'20071230') print 'Convert 2007-12-31' select Date=convert(datetime,'2007-12-31')
Results: Convert 20071230 Date ------------------------- 2007-12-30 00:00:00.000
(1 row(s) affected)
Convert 2007-12-31 Server: Msg 242, Level 16, State 3, Line 5 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
-
Okay, I stand corrected. I just never ran into a problem using yyyy-mm-dd format.
-
All,
Using this same query, I am trying to only pull duplicate records. This is not working it does pull duplicates but everything else as well. I can only find code to exclude duplicates by I want to include duplicates. SELECT X.CreatedWhen, X.OrderStatusCode, X.ReasonText, X.Active,
Y.BirthDayNum, Y.BirthMonthNum, Y.BirthYearNum, Y.DisplayName, Y.IDCode, Y.ApplicSource
FROM X INNER JOIN
Y ON X.ClientGUID = Y.GUID
WHERE (X.OrderStatusCode = 'PERF') AND (CONVERT(VARCHAR(10),X.CreatedWhen,112) LIKE '20070208')) and (X.ReasonText = X.ReasonText)
I also wanted to limit the query by month. I tried a BETWEEN statement but cannot get the syntax right.
AND (CONVERT(VARCHAR(10),X.CreatedWhen,112) between '20070208' and '2007010'))
Again, any help would be appreciated.
Thank you
-
It is almost always better to write a date range selection in this form:
where MyDateColumn >= StartDateTime and MyDateColumn < EndDateTeim
For example, to find all items for the date 2006-01-14.
Select * from MyTable Where MyDateColumn >= '20060114' and MyDateColumn < '20060115'
Notice that you are asking for greater than or equal to the beginning of the date, and less than the following date. You can apply the same general query for any range of days. This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.
To find all items for January 2007:
Select * from MyTable Where -- Greater than or equal to first day of month MyDateColumn >= '20070101' and -- Less than first day of next month MyDateColumn < '20070201'
Notice that the query dates are in format YYYYMMDD; you should always use this format for date strings. This is SQL Servers "universal" date format that works the same with all settings of DATEFIRST. Any other format may produce an error if the setting of DATEFIRST is not exactly what you expect.
For datetime strings use universal format
YYYYMMDD HH:MM:SS.MIL (20061231 23:59:59.997).
-
Select
*
from
MyTable
Where
-- Greater than or equal to first day of month
MyDateColumn >= '20070101' and
-- Less than first day of next month
MyDateColumn < '20070201'
This worked, I knew my format was bad.
Thanks. Would you know how to include only duplicates?
Thanks again.
-
Duplicates of what?
-
This is now my query:
SELECT Y.IDCode, X.CreatedWhen, X.OrderStatusCode, X.ReasonText,
X.Active, Y.BirthDayNum, Y.BirthMonthNum, Y.BirthYearNum, Y.DisplayName, Y.ApplicSource
FROM X INNER JOIN
Y ON X.ClientGUID = Y.GUID
WHERE (X.OrderStatusCode = 'PERF') AND (X.CreatedWhen >= '20070101') AND
(X.CreatedWhen < '20070201') AND (X.ReasonText = X.ReasonText)
I am trying to isolate duplicate x.ReasonText records. I thought by setting it equal to itself (this is what I do in crystal but it is not working here), it would give me duplicates. I do get duplicates but all other records as well.
Thank you,
-
GROUP BY Y.IDCode, X.CreatedWhen, X.OrderStatusCode, X.ReasonText, X.Active, Y.BirthDayNum, Y.BirthMonthNum, Y.BirthYearNum, Y.DisplayName, Y.ApplicSource
HAVING COUNT(*) > 1
-SQLBill
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply