SQL - CONVERT or whatever is used to take txt field CUSTOM_FIELD and extract date and then use in WHERE statement

Table : PHM_ENHANCED_CHGS
Field : CUSTOM_FIELD
Data type : text
Example : 201503302304

Field is a date time stamp.

I need to be able to do whatever is needed to the field data and then do a WHERE date between here and here.
ghettocounselorPharmacy Systems AdminAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Let's take the month of March 2015 as an example:

WHERE CUSTOM_FIELD >= '201503010000' AND CUSTOM_FIELD < '201504010000'

Date/datetime comparisons should use >= and < rather than >= and <=, even when they're stored in text :-).  That insures accuracy and performance even if the data type of the column changes.

Btw, never use functions against a table column unless it's absolutely unavoidable, because then SQL can't fully use indexes in that case.  In technical terms, the function makes it "nonsargable".
0
 
dsackerContract ERP Admin/ConsultantCommented:
Without knowing your table name or fields names, this may get you going:

SELECT *
FROM YourTable
WHERE CONVERT(datetime, MyStringField) BETWEEN '2015-01-01' AND '2015-03-31 23:59:59.997'

If your fields do not have the time with the date, you can try this:

SELECT *
FROM YourTable
WHERE CONVERT(datetime, MyStringField) BETWEEN '2015-01-01' AND '2015-03-31'
0
 
ghettocounselorPharmacy Systems AdminAuthor Commented:
Duh! sheesh, funny how sometimes you look at something and don't see the simplicity available. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.