Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting a string to a date Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 12:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.
Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.

I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.

SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...

Any help at all would be greatly appreciated.
Post #1382676
Posted Thursday, November 8, 2012 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 13,007, Visits: 12,421
dj1202 (11/8/2012)
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.
Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.

I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.

SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...

Any help at all would be greatly appreciated.


What you are describing is one of the reasons you should store datetime data in a datetime column.

Can you post some ddl, sample data and desired output. Take a look at the first link in my signature for best practices when posting questions.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382683
Posted Thursday, November 8, 2012 5:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 2,123, Visits: 3,211

Hmm, it's not clear to me what "fields" 2 and 3 contain, so I'll assume field2 has the "4Q" and field3 has "12".

Just in case field1 is indexed, or it could be some day, let's avoid using functions on it (which is good technique anyway):

SELECT *
FROM dbo.tablename
WHERE Field1 >= '20' + field3 + CASE field2
WHEN '1Q' THEN '01'
WHEN '2Q' THEN '04'
WHEN '3Q' THEN '07'
WHEN '4Q' THEN '10' ELSE '01' END + '01' AND
Field1 < DATEADD(QUARTER, 1, '20' + field3 + CASE field2
WHEN '1Q' THEN '01'
WHEN '2Q' THEN '04'
WHEN '3Q' THEN '07'
WHEN '4Q' THEN '10' ELSE '01' END + '01')



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1382779
Posted Saturday, November 24, 2012 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:57 AM
Points: 1, Visits: 240
If Field2 and Field3 are qtr/year values then you can re-arrange them to YY'Q'QTR so it's sortable. Next convert Field1 to the same format which will allow you to filter using BETWEEN.

IF object_id('tempdb..#tbl') IS NOT NULL
BEGIN
DROP TABLE #tbl
END

-- Assumptions
-- 1) Field2 <= Field3
-- 2) Valid ranges for Field2 and Field3 are in the expected format and between [2000, 2099]
-- 3) Field1 values can be converted to valid dates between [1/1/2000, 12/31/2099]
-- 4) You are unable to implement a more appropriate design to the table at this time

-- Create some test data
CREATE TABLE #tbl (
Field1 VARCHAR(8),
Field2 VARCHAR(4),
Field3 VARCHAR(4)
)

INSERT INTO #tbl(Field1, Field2, Field3)
VALUES('20120908', '4Q12', '1Q13') -- Field1 is before range
, ('20121108', '4Q12', '1Q13') -- Field1 is in range
, ('20130408', '4Q12', '1Q13') -- Field1 is after range

-- Test select
SELECT *
FROM #tbl
WHERE RIGHT(LEFT(Field1, 4), 2) + 'Q' + CAST((MONTH(CONVERT(DATE, Field1, 112)) - 1) / 3 + 1 AS VARCHAR)
BETWEEN RIGHT(Field2, 2) + 'Q' + LEFT(Field2, 1) AND RIGHT(Field3, 2) + 'Q' + LEFT(Field3, 1)

Post #1388317
Posted Saturday, November 24, 2012 6:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:52 AM
Points: 369, Visits: 1,215
Indeed, you are making a crucial mistake by not using appropriate datatypes.
The penalty you give yourself is tons of problems you will experience now and in the future, and even more time to fix them.
Cheaper and faster is to use the right datatype for all columns. In this case, date.

Field1 string with format YYYYMMDD would become real date, not string.
Field2 and Field3 with format QQYY would also become real date. E.g. 2Q12 would become date 2012-04-01.
It will be easier and cleaner if you create deterministic function to convert from QQYY to date.

If you do not want to change existing fields, alternative is to add 3 new calculated non-persistent fields of datatype DATE,
create indexes on those field(s) and use those calculated fields in your WHERE clause.
It will be much much faster because you have index on that field and optimizer can use it because there is no function around that field.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1388354
Posted Sunday, November 25, 2012 8:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 1,945, Visits: 3,020
I have 3 fields,


Columns are not fields; totally different concept.

all text strings are supposed to represent dates.


..and one of the many ways they differ is that a column has a data type and constraints. A field gets its meaning from the host program that reads it. Please read a book on basic RDBMS and then one on SQL.

Field 1 is varchar in the format of '20121108'.


declare this as DATE. When you read that book on SQL, you will find the the only ISO-8601 display format is yyyy-mm-dd.

Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.


Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. In your case, I would use:

CREATE TABLE ..
( ..
field2 CHAR(6) NOT NULL CHECK (field2 LIKE '[12][0-9][0-9][0-9]Q[1-4]',
field3 CHAR(6) NOT NULL CHECK (field3 LIKE '[12][0-9][0-9][0-9]Q[1-4]',
CHECK (field2 <= field3),
..);

>> I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field <<

SELECT P.report_name, F.field1, ..
FROM Foobar AS F, Report_Periods AS P
WHERE F.field1 BETWEEN P.start_date AND P.end_date;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1388406
Posted Sunday, November 25, 2012 11:54 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
dj1202 (11/8/2012)
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.
Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.

I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.

SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...

Any help at all would be greatly appreciated.

You have multiple problems here. First, the "Field 1" date needs to be cast/converted to a proper date datatype at some point. Eventually, and at the worst possible time (Murphy's Law), this is going to fail if the varchar "date" is not a valid one (e.g., '20121131'). And by the way, SQL's ISDATE function has some serious flaws so you need to be careful when using that. Here's a discussion on a similar topic: Get date part from a filename string

So if it's not possible to fix the source data, then I'd recommend writing a query to import the dates into an intermediate staging table that could be validated before being used in a production query. This could be time-consuming depending on where you are getting the data from, how often it changes, etc. But if the data import is an infrequent process relative to the query itself then getting the data validated and into properly typed columns FIRST will avoid inevitable datetime datatype errors and the need to use CAST/CONVERT in the WHERE clause of your production query--which is generally not a good idea.

Then of course you have the problem of interpreting what '4Q12' is so that date logic can be applied to it. If you choose to import to a staging table, your import procedure/query can use some of the ideas posted above by others to put these quarter/year strings into the proper date format according to your particular business rules. If these quarter boundaries are going to be static then you should probably just create a separate table (as noted in the post just above).

Thus, once you have validated and properly typed columns the WHERE date1 BETWEEN date2 and date3 part of the query is easy. (Keep in mind that the BETWEEN clause is INCLUSIVE when setting up your quarter boundaries.)

Of course, you will have to compare the performance issues of importing, validating, and otherwise "cleaning up" and converting the data first against the performance of a query or procedure doing all of this "on the fly." That will depend on how often you do one or the other or both.

 
Post #1388441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse