January 5, 2005 at 1:12 pm
I need to select rows from Contacts where Contacts.ModDate (column data type = "datetime") is equal to today's date. Contacts.ModDate is stored in this format:
7/6/2004 9:41:14 AM
I receive the following errors when running these scripts with changes made to the last line of the script before each run. How do I tell SQL what the datetime format is so that this query will work?! Thanks a lot! --S
SELECT Contacts.LastName, Contacts.FirstName, Contacts.MiddleName,
Contacts.Salutation, Contacts.Suffix, Contacts.Title, Contacts.Address1,
Contacts.City, Contacts.State, Contacts.ZIP, Contacts.Country, Contacts.Phone, Contacts.Fax, Contacts.Pager, Contacts.CellPhone, Contacts.HomePhone, Contacts.EMail
FROM Contacts
WHERE
Contacts.ModDate = 'currentdatetime'
ERROR:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Contacts.ModDate = '" & date() & "'
ERROR:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Contacts.ModDate = ' & date() & '
ERROR:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Contacts.ModDate = '" & datetime() & "'
ERROR:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
January 5, 2005 at 1:26 pm
I see two possible solutions.
Contracts.ModDate = GETDATE(). This may cause problems with the time part. To overcome that and actually only match on the actual date without the time.
CONVERT( varchar, Contracts.ModDate, 101) = CONVERT( varchar, GETDATE(), 101) should work. This second solution basically strips the time element off.
I wasn't born stupid - I had to study.
January 5, 2005 at 2:12 pm
Excellent - Thanks! I had just got it to work (rather accidentally - with this code) - when I read your post suggesting the same technique. THANK YOU!!
SELECT Contacts.LastName, Contacts.FirstName, Contacts.MiddleName,
Contacts.Salutation, Contacts.Suffix, Contacts.Title, Contacts.Address1,
Contacts.City, Contacts.State, Contacts.ZIP, Contacts.Country, Contacts.Phone, Contacts.Fax, Contacts.Pager, Contacts.CellPhone, Contacts.HomePhone, Contacts.EMail, Contacts.ModDate
FROM Contacts
WHERE
CONVERT(char, Contacts.ModDate) > GETDATE ()
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply