0

I am working in SQL Server 2008 R2

I need to convert a varchar(50) field in a view to a date format.

I have a view that uses the following to create the field delivered_date:

convert(varchar(50),[delvd_dt],110) as [delivered_date]

The formatted field looks like : 2012-03-11 16:24:42.0000000

I need the results to be a date so that I can check for dates within a range. I would prefer to do it within the view so that I can use SSRS to create the range for the report.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim Vavra
  • 537
  • 1
  • 19
  • 35
  • possible duplicate of [Convert varchar into datetime in SQL Server](http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server) – AJ. Apr 25 '13 at 13:56
  • 1
    Well, why don't you FIX the table and store it in the right data type in the first place? – Aaron Bertrand Apr 25 '13 at 14:15

3 Answers3

3

Have you tried cast()? The following returns the right date for me:

select CAST('2012-03-11 16:24:42.0000000' as DATE)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Perhaps you can simply truncate the millis and use CONVERT:

SELECT Convert(datetime, LEFT('2012-03-11 16:24:42.0000000', 19), 102) AS [Date]

Demo

This works even in MS SQL-Server 2005.

CAST and CONVERT (Transact-SQL)

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
-3

Try this link website shows several formatting options.

Example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
Sharad
  • 3,562
  • 6
  • 37
  • 59
  • Goes the wrong direction. He already has a character, wants to get a datetime. – Joel Coehoorn Apr 25 '13 at 14:03
  • The confusing part of the quote is the word "format". He's saying he already has a varchar(50), and he wants a date of some kind. Replace "format" with "type" and it makes more sense. – Joel Coehoorn Apr 25 '13 at 14:07