Date record retrieval

Other SQL Server Topics
1

Hello

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

Any help?

Thanks

2
  1. is this for Microsoft sql server database?
  2. what data type is the column date in where date =
3

Yes Sqlserver 2014

The data type is showing as datetime

4

this is not datetime
20170313095154. you might have to convert it to datetime

5

SELECT * from table where date='20170313 09:51:54'

6

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.
Thanks

7

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)

8

stored as 20170313095154? are you using SSMS to look at the data or some other query tool?

9

Thank you all. I found another source table that I can use to get the records with date specific.

10

I am using sql server studio.