I have a datetime column that stores date example 20170313095154
when I try to query this I am getting an error
SELECT * from table where date=20170313095154
Arithmetic overflow error converting expression to data type datetime.
SELECT * from table where date='20170313095154'
Conversion failed when converting date and/or time from character string
Yes Sqlserver 2014
The data type is showing as datetime
this is not datetime
20170313095154. you might have to convert it to datetime
SELECT * from table where date='20170313 09:51:54'
Thanks Scott, the query worked but it is not retrieving records with the date if I use it in this format. The column is defined as [date] [datetime] NULL in the table and have values stored in format 20170313095154. Is there a function that I can use to translate it another datetime and get the correct records.
If it's defined as datetime, it has a special internal format, which looks nothing like an actual date.
It looks like it must be a bigint, in which case you'd need to do this:
SELECT * from table where date=CAST(20170313095154 AS bigint)
stored as 20170313095154? are you using SSMS to look at the data or some other query tool?
Thank you all. I found another source table that I can use to get the records with date specific.
I am using sql server studio.