Development How do I write a SQL query involving a time stamp?

How do I write a SQL query involving a time stamp?

QUOTE_ID is a character string
CREATE_DTG is a date time type
RETENTION_LEAD_TRACK is a table

This sql statement cmf.CommandText = "SELECT QUOTE_ID FROM RETENTION_LEAD_TRACK where CREATE_DTG > '2016 - 04 - 25 18:18:15.2891'"

throws this error {"Conversion failed when converting date and/or time from character string."}

 

What sql database are you writing this for?

 

Clearly MS SQL Server based on error.

So either use 'YYYY-MM-DDTHH:MM:SS' format, or use the CONVERT function in form of convert(datetime,'DATESTRING',FORMAT) :
https://msdn.microsoft.com/en-us/library/ms187928.aspx

 

spaces between the YYYY-MM-DD may be your issue?

 

I would use CONVERT to change it in a query

 

I think it all depends on where your actual date is coming from...

But if push comes to shove, use CAST over CONVERT because it is ANSI compliant and preserves precision:

Code:

...WHERE CREATE_DTG > CAST('2016 - 04 - 25 18:18:15.2891' AS DateTime)