1

I have a colum that is showing a date as a string. It comes through as '07/01/2021 01:03:47 AM' I am trying to query so that I can pull all data where the date is greater than '07/01/2021 01:03:47 AM'.

I have tried select * from datatable where date> '07/01/2021 01:03:47 AM'

I also tried select * from datatable where cast(coldate as timestamp)> '07/01/2021 00:00:00'

but because it is a string it won't pull the correct view. I will show a random collection of data due to the string.

Wesley DeLuca
  • 11
  • 1
  • Please edit your question and add a tag for the specific type of SQL you're working with. – devlin carnate Jul 23 '21 at 21:19
  • 1
    ..store the value as a date instead of a string. – alexherm Jul 23 '21 at 21:20
  • Troubleshooting tip: Try `SELECT cast(coldate as timestamp) FROM datatable` . Does that explain why it doesn't work? – devlin carnate Jul 23 '21 at 21:21
  • Does this answer your question? [Sql Server string to date conversion](https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) – devlin carnate Jul 23 '21 at 21:22
  • when I use SELECT cast(coldate as timestamp) FROM datatable the date comes back as date field comes back as null. – Wesley DeLuca Jul 23 '21 at 21:26
  • Date functions are very specific to the RDBMS. Which one are you using? – dougp Jul 23 '21 at 22:01
  • You might be able to get away with something like this, but you will need to provide the appropriate format strings for ```TO_DATE``` and ```TO_CHAR``` - ```to_char(to_date(date_field,'yyyymmdd'),'dd-Mon-yyyy')``` – alexherm Jul 23 '21 at 22:25

1 Answers1

0

Try this

Cast('7/1/2021' as datetime)

and

Convert(varchar(30),'7/1/2021',102)

See CAST and CONVERT (Transact-SQL) for more details.

Caleb Farara
  • 263
  • 1
  • 7