Using substring function within the convert function in SQL Server

Question

SQL Server: can I convert a varchar into substring and then convert to datetime to compare with a datetime range in a single SQL query?

I need this in the where clause

Have you checked http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion ? — wldsvc, Apr 18 '15 at 03:06
Look this page http://www.sql-server-helper.com/tips/date-formats.aspx — Nizam, Apr 18 '15 at 03:08
Have you looked into this. http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion how is your question different — Panther, Apr 18 '15 at 03:16

score 0 · Answer 1 · answered Apr 18 '15 at 07:40

Technically speaking, no - you can't convert something to a substring because that is not a datatype. You can however convert a varchar to a datetime, by utilizing substring if you need to.

Take the following example

myStringWithDate
------------------------------
'This date, 2015-01-01 08:00:00, is...'

SELECT * FROM myTable
WHERE getDate() <= CAST(SUBSTRING(myStringWithDate, 12, 19) AS DateTime)

In this example, substring will return '2015-01-01 08:00:00' and then cast this to datetime to compare against getDate() (system current DateTime).

I hope I counted the substring correctly. Couldn't verify it, but it should be correct.

score 0 · Answer 2 · answered Apr 18 '15 at 07:51

It can be done. T-Sql allows you to nest the SUBSTRING function within a CAST or CONVERT function.

The best way to compare ranges is with two conditions comparing the beginning and end of the range.

Here is some test data:

Create Table VarCharDateTest --Create Table with dates embedded in a varchar string
(
vID int identity(1,1),
vDate varchar(50)
)
GO

INSERT INTO VarCharDateTest --Insert Test Values
VALUES
('The Date is 04/18/2015, a Saturday'),
('The Date is 04/19/2015, a Sunday'),
('The Date is 04/20/2015, a Monday'),
('The Date is 04/21/2015, a Tuesday')
GO

CREATE TABLE DatetimeTest --Create table with dates stored as datetime
(
dID int identity(1,1),
dDate datetime
)
GO

INSERT INTO DateTimeTest --Insert Test Values
VALUES
('4/12/2015'),
('4/13/2015'),
('4/14/2015'),
('4/15/2015'),
('4/16/2015'),
('4/17/2015'),
('4/18/2015'),
('4/19/2015'),
('4/20/2015'),
('4/21/2015'),
('4/22/2015'),
('4/23/2015')
GO

DECLARE @startDate datetime = '2015-04-18 00:00:00.000'
DECLARE @endDate datetime = '2015-04-21 00:00:00.000'

SELECT
    dID, 
    dDate, 
    vID, 
    vDate,
    cast(Substring(vDate, 12,11) as datetime) as DateConvertedFromVarchar  --explicit conversion to datetime
FROM
    DatetimeTest
INNER JOIN
    VarCharDateTest
    ON dDate = cast(Substring(vDate, 12,11) as datetime)
WHERE
    cast(Substring(vDate, 12,11) as datetime) > @startDate AND
    cast(Substring(vDate, 12,11) as datetime) < @endDate

Using substring function within the convert function in SQL Server

2 Answers2