CODEWITHSUNDEEP
sqlsql-server
wakthar
wakthar

Reputation: 718

Convert string to datetime in sql

I have a database table with 3 columns being ID, Key and Value. The ID field is an int, Key is varchar(50) and Value is varchar(100).

My sample data for this table is as follows:

ID          Key                 Value
1           Template            one
2           RequestedOn         15/04/2014 12:12:27
3           PrintedOn           15/04/2014 12:12:37
4           Template            two
5           RequestedOn         16/04/2014 12:22:27
6           PrintedOn           16/04/2014 12:22:37
7           Template            three
8           RequestedOn         17/05/2014 12:32:27
9           PrintedOn           17/05/2014 12:32:37
:
:
45          RequestedOn         17/06/2014 12:22:27
46          PrintedOn           17/06/2014 12:22:37
47          Template            three
48          RequestedOn         17/06/2014 12:32:27
49          PrintedOn           17/06/2014 12:32:37

I want to be able to query the table to return values between certain date ranges.

For example:

I want to return all rows where PrintedOn is between 17/06/2014 12:22:27 and 17/06/2014 12:32:37

I have tried the following query but get 'Conversion failed when converting character string to smalldatetime data type.' message.

SET DATEFORMAT DMY
;with cte as
(
select CONVERT(datetime, CAST(Value as smalldatetime)) as PrintedOn
from ExtendedProperties 
where isdate(Value) = 1 
)
select * from cte where PrintedOn > '17/05/2014' and PrintedOn < '17/06/2014'

Upvotes: 0

Views: 1518

Answers (4)

Serpiton
Serpiton

Reputation: 3684

Disclaimer: The OP didn't specified the version of SQLServer he is using in the answer it is assumed as SQLServer 2012 or better.

The first step should be to get the data in shape with a PIVOT or a fake PIVOT.

Using the hypothesys that the key 'Template' will always precede the other two this can be done with this

SELECT [Template]
     , Try_Parse(RequestedOn as DATETIME2 USING 'it-IT') RequestedOn
     , Try_Parse(PrintedOn as DATETIME2 USING 'it-IT') PrintedOn
FROM   (SELECT [Key], Value
             , ID = SUM(CASE WHEN [Key] = 'Template' THEN 1 ELSE 0 END)
                   OVER(ORDER BY ID)
        FROM   Table1) d
       PIVOT
       (MAX(Value) FOR [Key] IN ([Template], [RequestedOn], [PrintedOn])) u

The Try_Parse is using the italian culture because it's one ofthe country where the date is in the format dd/MM/yyyy, using a culture that have a different format will result in NULL values.

Having that everything is a matter of querying the VIEW/CTE, here I'll use a CTE

With T AS (
  SELECT [Template]
       , Try_Parse(RequestedOn as DATETIME2 USING 'it-IT') RequestedOn
       , Try_Parse(PrintedOn as DATETIME2 USING 'it-IT') PrintedOn
  FROM   (SELECT [Key], Value
               , ID = SUM(CASE WHEN [Key] = 'Template' THEN 1 ELSE 0 END)
                     OVER(ORDER BY ID)
          FROM   Table1) d
         PIVOT
         (MAX(Value) FOR [Key] IN ([Template], [RequestedOn], [PrintedOn])) u
)
SELECT *
FROM   T
WHERE  PrintedOn BETWEEN '20140617 12:22:27' and '20140617 12:32:37'

SQLFiddle demo

The result will be in the format

Template | RequestedOn | PrintedOn
---------+-------------+----------
value    | date        | date

Upvotes: 2

g2server
g2server

Reputation: 5367

If using SQL Server 2012 you can replace your CAST with TRY CAST. here is an example of syntax

Declare @string as varchar(7)
Set @string ='raresql'    
SELECT Try_Cast(@string as smalldatetime) as [Cast Text to smalldatetime]

In this case, your values like "three" will return null instead of exception.

Upvotes: 1

Dave C
Dave C

Reputation: 7402

Here is a fiddle... of the solution.

create table test (value varchar(100))

insert into test
select 'one' union
select '15/04/2014 12:12:27' union
select 'two' union
select '16/04/2014 12:22:27'

set dateformat dmy
;with cte as
(
select case when isdate(value)=1 then convert(datetime, value) else null end as PrintedOn
from test 
where isdate(Value) = 1 
)
select * from cte

Upvotes: 0

bummi
bummi

Reputation: 27385

Using CASE you are able to convert only if Value contains a date

;with cte as
(
select  Case when isdate(Value) = 1  then CAST(Value as datetime) else NULL end  as PrintedOn
from @t 
where Case when isdate(Value) = 1  then CAST(Value as datetime) else NULL end is not NULL
)
select * from cte where PrintedOn > '17/05/2014' and PrintedOn < '17/06/2014'

Upvotes: 0

Related Questions