t-sql datetime conversion issue

Question

t-sql datetime conversion issue

wendy elizabeth

SSCoach

Points: 16915
More actions
March 17, 2014 at 11:18 am

#291069

In a sql server 2012 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 smalldatetime = '01012014'
DECLARE @enddate smalldatetime = '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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

It sounds like you think the date you are getting is formatted MMDDYYYY, but is really formatted DDMMYYYY. Either that or local settings are tripping you up.

The actual example you gave is ambiguous and your statement converts it fine.

So I'd first analyze the data and verify the format (which is the month). Also, I'd specify the format code in the convert statement. There is a list of them here. You can add it as the third argument, and then you can make sure that the DB knows how you want to translate the string.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

wendy elizabeth

SSCoach

Points: 16915

March 17, 2014 at 3:49 pm

#1697815

got the sql to work

murdakillemall Mr or Mrs. 500 Points: 527 More actions · Answer 3

Just a note, in my experience, it seems easier to convert (or cast) the string to date in the YYYYMMDD format as there is no separator involved and this works across collations. If I remember correctly, SQL Server stores all dates in this format internally.

BrainDonor SSCoach Points: 19243 More actions · Answer 4

murdakillemall (3/18/2014)
Just a note, in my experience, it seems easier to convert (or cast) the string to date in the YYYYMMDD format as there is no separator involved and this works across collations. If I remember correctly, SQL Server stores all dates in this format internally.

Not quite. SQL Server stores dates as integers for the number of days since 1st January 1900, with a negative value if the date is before this base date. DateTime is two 4-byte integers, one for the number of days and the other for the time.

A good article by Kalen Delaney on this - http://sqlmag.com/sql-server/solving-datetime-mystery.

And if I haven't bored you enough with this:

According to MS YYYYMMDD is part of the ISO 8601 standard for date formatting and so is the a way of entering a recognisable date, but it will be stored as a 4-byte integer.

There is also some dispute as to whether the MS docuumentation is correct in repect to this:

https://connect.microsoft.com/SQLServer/feedback/details/814796/try-parse-does-not-parse-iso-8601-yyyymmdd-international-date-format

Steve Hall
Linkedin
Blog Site

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Now that you have figured out how to convert your character data to a datetime I would HIGHLY recommend you change the datatype so you don't have to wrestle with this anymore.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

wendy elizabeth (3/17/2014)
got the sql to work

So, what did you do to get it to work? Two way street here. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions