Indentify Record within Period

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 2008 Forums

Transact-SQL (2008)

Indentify Record within Period

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2010-09-21 : 12:22:09

I have a transaction table and I have a account table.

In the account table I have a StartDate and EndDate both are DataTypes nVarChar(50)

In the transaction table I have bookings with a EnterDate (nVarChar(50))

I need to identify which records in the Trans table fall within the Start and End Date of the account table.

This I need to show in a SQL View.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 12:24:51

post the ddl wih sample data

but why are the dates stored as varchar...and why unicode?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:26:39

how are you storing the dates i.e in what format?
By using nvarchar fields you're only making the date manipulations really hard for yourself. Always try to use proper datatypes for your fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2010-09-21 : 12:31:59

I agree it would be better stored as DateTime fields but the principle of the SQL would still be the same.

I can get this to work if I link MS Access to the tables and write the query there but if I transfer this to SQL Server the SQL does not work.
This is the SQL in MS Access

SELECT IIf(Transactions.ConnectTime<Conf_Dates.StartDate,'Yes','No') AS StartOK
FROM Transactions LEFT JOIN Conf_Dates ON CDR_Transactions.ConfRefNum = Conf_Dates.ConfRefNum;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:36:25

quote:
Originally posted by ConfusedAgain

I agree it would be better stored as DateTime fields but the principle of the SQL would still be the same.

I can get this to work if I link MS Access to the tables and write the query there but if I transfer this to SQL Server the SQL does not work.
This is the SQL in MS Access

SELECT IIf(Transactions.ConnectTime<Conf_Dates.StartDate,'Yes','No') AS StartOK
FROM Transactions LEFT JOIN Conf_Dates ON CDR_Transactions.ConfRefNum = Conf_Dates.ConfRefNum;

not always
storing it as varchar will cause problems when you're trying to do comparisons among values which is exactly what you want in this case.
so in your case you've actually convert them back to dates before you can compare. for that you need to determine what all formats they're storing dates currently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2010-09-21 : 12:54:28

Ok if I can convert them to DateTime datatype how would display which records fall between these dates.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-21 : 13:33:49

Use the CASE statement
SELECT CASE WHEN Transactions.ConnectTime < Conf_Dates.StartDate THEN 'Yes' ELSE 'No' END as StartOk

Jim

Everyday I learn something that somebody else already knew

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2010-09-21 : 13:47:14

Thank you kindly Jim, that works a treat.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 13:55:17

doesn't it have to between 2 datetimes?

The other thing about varchar dates...is that they may NOT be datetime values in there...then youj're hosed unless you account for that as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2010-09-21 : 14:23:23

They do have to be between to date times but I can add another expression that show if it is before the end time.
I will try to convert these to DateTime Fields if I do this will the same query work do you know?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources