t-sql datetime conversion issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql datetime conversion issue

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-03-17 : 13:17:03

In a sql server 2008 r2 database, I have a column called TransactionPaymentDate. The column is defined
as varchar(50). The value in the column looks like '05012014'. I need to compare the value of this field
with data fields that use datetime or smalldatetime.

I have tried the following sql statements so I can convert the value to datetime or smalldatetime:

1. CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2))
**this gives an error message: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

2. TransactionPaymentDate = cast(substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2) as smalldatetime
This does not work in a where statement.

I want to use to be able to do the following type of a compdarsion:
DECLARE @startdate varchar(50) = '01012014'
DECLARE @enddate varchar(50) = '03192014'

Select * from cust_table
where TransactionPaymentDate between @startdate and @enddate.

I try the following sql, and I have problems:

Select * from cust_table
where convert(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2))
between @startdate and @enddate

Thus can you show me sql that will work for this date comparison I want to do?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 16:49:58

Assuming that '05012014' represents May 1, 2014, and assuming that the data is consistently in that format with always 8 characters, you can do this as in the example below:

DECLARE @x VARCHAR(8) = '05012014';
SELECT CAST(RIGHT(@x,4)+LEFT(@x,4) AS DATETIME);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-17 : 17:38:57

I don't suppose there us any chance you can fix the database to use the proper data type? Is this an internal database or one form a vendor? I'd ask whomever created it if they have malpractice insurance.

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-03-17 : 17:47:43

found the answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources