Comparing datetime split across columns

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

SQL Server Development (2000)

Comparing datetime split across columns

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 06:23:43

Having got totally fed up with a Windows/SQL 2000 machine randomly and, despite many attempts, constantly deciding it lives in the USA and not the UK and therefore using mm/dd/yyyy instead of dd/mm/yyyy, I have split datetimes up over several columns to regain control (I hope!). I therefore have a table with columns for active_to_hour, active_to_minute,..etc.. My problem is getting the snytax right for comparing the date/time held in the seperate columns with the current date/time. This is what I have at the moment, but I have a feeling there is a more elegant way..

select * from
content
where

active = 1
and
active_to_year > convert(int,datepart(year,getdate()))

or

active = 1
and
active_to_year = convert(int,datepart(year,getdate()))
and
active_to_month > convert(int,datepart(month,getdate()))

or

active = 1
and
active_to_year = convert(int,datepart(year,getdate()))
and
active_to_month = convert(int,datepart(month,getdate()))
and
active_to_day > convert(int,datepart(day,getdate()))

or

active = 1
and
active_to_year = convert(int,datepart(year,getdate()))
and
active_to_month = convert(int,datepart(month,getdate()))
and
active_to_day = convert(int,datepart(day,getdate()))
and
active_to_hour > convert(int,datepart(hour,getdate()))

or

active = 1
and
active_to_year = convert(int,datepart(year,getdate()))
and
active_to_month = convert(int,datepart(month,getdate()))
and
active_to_day = convert(int,datepart(day,getdate()))
and
active_to_hour = convert(int,datepart(hour,getdate()))
and
active_to_minute > convert(int,datepart(minute,getdate()))

or

active = 1
and
active_to_year = convert(int,datepart(year,getdate()))
and
active_to_month = convert(int,datepart(month,getdate()))
and
active_to_day = convert(int,datepart(day,getdate()))
and
active_to_hour = convert(int,datepart(hour,getdate()))
and
active_to_minute = convert(int,datepart(minute,getdate()))
and
active_to_second > convert(int,datepart(second,getdate()))

order by created_year DESC,
created_month DESC,
created_day DESC,
created_hour DESC,
created_minute DESC,
created_second DESC

I will also need to check the active_from columns as well, and I don't want to convert anything into a smalldatetime datatype.. Any ideas?

Jack

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-13 : 06:34:45

yuck!

OK, take a deep breath

I know that Byrmol and NR are going to jump on me for this. But I find calling dates in this format '13-Feb-2002' works every time.

Damian

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 06:50:21

OK, but how do you compare that with the current datetime?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 07:34:44

Well, splitting them up into separate columns is not gonna solve it.

Take a look at the entry for CONVERT() in Books Online. Look at the format table for date conversions. Damian's format is 106, you can also use 112 (yyyymmdd). This is the ISO date format and it ALWAYS gets recognized correctly by SQL Server. If you need the timestamp included, 120, 121 and 126 might work for you too.

Where does the problem come up? Data entry? Date arithmetic? Once it's stored in the database (properly) the only issue would be date format for output (query analyzer returns GetDate() in 121 format), and using CONVERT() will handle that for you.

Comparing dates can be done with DateDiff(); what are you comparing for? If it's the same day, then DateDiff(dd, date1, date2)=0.

I can't quite figure out what your query is doing, but the mixed AND and OR operators are unnecessary. Something like this should work:

SELECT * FROM content
WHERE active = 1 AND active_to_date > getdate()
ORDER BY active_to DESC

I made up an active_to_date column, it would be a date value composed of the individual values you're storing now.

Edited by - robvolk on 02/13/2002 07:40:48

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 08:42:56

I've had the problem with data entry, display and comparison. For instance a date of 12/01/2002 entered form a form inserts into the db as 01/12/2002, also had UK dates in the db which read back as US on an ASP page and Response.Write NOW in an ASP page changing from UK to US and currency formats changing as well. Sometimes it seems to be SQL Server, sometimes IIS and sometimes ADO. Got to the stage where there is a shortcut to reset the registry settings to UK on the desktop. Thats why I'm thinking of spltting it up into seperate columns.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-13 : 08:53:50

my advice is to ALWAYS include the following when working with dates....be that read, writing, updating in place or whatever...

SET DATEFORMAT DMY (or YMD whichever you prefer)
put it at the top of any SP or in-line SQL.

after that it's a matter of insisting that your front end operates under 1 date-format in the registry. (you can put in code to validate that it's set properly, before any important work gets done)

always remember to test it with a days value <= 12....since a days value > 12 automatically get's interpreted the right way. it's a pain in the butt getting going with dates, but once you get comfortable with what works, stick with it.....(and if yyyymmdd always works, then it might be worth looking into).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 09:01:07

Whatever you do, DON'T split date components into separate columns. If you think you're going nuts now, wait a month after doing THAT and you'll be in the looney bin chewing on the rubber walls.

Forget about the registry settings. The problem most likely lies in ASP and IIS. You should write your own custom date conversion function that takes a date value and formats it to UK specs; don't use the FormatDateTime VBScript function.

Also, if you know a little bit of JavaScript (trust me, you don't need to know much at all), you can write the function in JScript using date objects. There are a bunch of very easy-to-use date constructor methods. It can also do format conversions and date arithmetic just as easily (or more easily) than SQL Server.

This should help you get started:

http://msdn.microsoft.com/library/en-us/script56/html/js56jsobjdate.asp

Don't let the Java(Script) Date object intimidate you; it's really easy to use and will most likely solve all your problems (in fact, the link has an example JScript function that does EXACTLY what you need).

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 09:22:54

Ok thanks robvolk, I'll give that a go. It's just frustating that every workaround I try seems to either a; not work b; require lots or rewriting of code (not written by me!!) or c; work for a while until someone restarts the server. You'd think you'd just be able to install IIS and SQL with UK setting and leave it at that, but oh no - well I guess we did lose the War Of Indepence..

quote:

you'll be in the looney bin chewing on the rubber walls.

..what flavours do they have?

..actually come to think of it I still think splitting it up across columns is a great idea, just think how easy it would be to select all records from january for every year or something like that, I'd just need to work the syntax out for comparisons. Anyone out there want to back me up?

they're coming to take me away ha, ha

Edited by - jackstow on 02/13/2002 09:54:07

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 10:43:30

quote:
..what flavours do they have?

Hockey mask...Have the lambs stopped screaming, Clarice?

Your lambs haven't started screaming yet Jack, but they will! Not sure exactly what data your table is storing, so I'm gonna use a simple Orders table. Here's a sample:

-Find the number of days between the last two orders for each customer. Now do it across monthly boundaries. Then yearly boundaries. Then do it counting business days only.
-A report with the number of orders for each WEEK, grouped by week. Then do a quarterly report like this.
-A report for all orders on each weekday.
-All orders in Janaury? No problem! All orders on the 1st, 2nd, and 3rd weeks of January? Uhhhhhh...

I think you see where I'm going. Anything other than the very most basic date arithmetic, and keeping date parts in separate columns will have the lambs caterwauling like banshees in no time. You CAN do these reports with separate columns, but you'd have to recreate every date value, and I think the conversion would look something like:

SELECT Convert(datetime, Str(active_to_day, 2, 0) + '/' + Str(active_to_month, 2, 0) + '/' + Str(active_to_year, 4, 0), 103) FROM myTable

That'll give you ONE date value, if you need to use DateDiff, you'd have to convert each date like that.

As to the question, "Yeah Rob, but I don't need to do any reports like that", I answer, "Not yet, but you will!"

You can always use DateName and DatePart to extract portions of the date that you need. Normally I would agree that keeping things separate (last name and first name) is better, because it's a lot easier to put them together rather than take them apart, but in the case of date values, it's better to keep them all together in one column.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 11:12:42

I see where you're coming from and I know that you're bound to be right, but wouldn't it be better to jump through hoops a bit and build up dates from seperate columns knowing that you've got the right data rather than doing it from one column and not being 100% sure that you've got the data from 12 January and not 01 December.. Maybe its just the voices in my head.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-13 : 11:33:09

The problem isn't based in SQL Server or date storage, it's strictly a data entry/user interface issue. Splitting the date parts into separate columns doesn't solve that problem, it accommodates it, AND creates new ones. I can't see how needing to apply a lot of extra SQL code to work with dates is better, just because it saves you from dealing with the problems in the ASP/VB interface code.

How do the date values go in now? Are there separate input boxes for month, day, and year? If so, you can always assemble a properly formatted date BEFORE you pass it to SQL Server (the JavaScript date object is perfect for this; it will throw an error if the date is invalid) If the date is generated automatically, instead of passing Now() or Date() from ASP to SQL Server, simply use GetDate() internally in SQL Server. You should always use GetDate() on the SQL Server side if you need a system-generated date.

Another option is to check some web developing sites (www.dynamicdrive.com is a good one) and look for calendar or date-picker controls. These are usually pretty small (4-5K of code), and they greatly simplify date entry. People just click on a date and it will set the date value perfectly. If they have to type in date values now, they'll love it if you put in something like that.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-13 : 12:18:06

Oh yeah, I always use getdate() as a default value and I'm working on a date input interface based on the ASP.NET calendar control right now so I hear you there. The problem is that the date IS properly formatted before it is inserted - its when you look in the table after the insert that it has changed to US format. And getdate() gives you UK format when used as a default value. That's why I'm suspicious of ADO swapping the dates around somehow between the page and SQL Server. I dunno, its been a long day...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-13 : 14:21:31

I have to agree 100% with Rob on this

. Keep your date as a date and use the functions he mentions. It sounds from what you're describing that however the data is stored, you will have to deal with the flip-flop. (By the way, welcome to the colony

).

I didn't see this specifically mentioned, but I'll throw it out there... remember when you're comparing date values to getdate() that getdate() also returns a time portion so you could end up comparing 2002-02-13 00:00:00 to 2002-02-13 11:20:00 and get messed up. To get around this, do a Datediff or just convert(varchar(8), getdate(), 106).

Can I get my walls in chocolate?

------------------------
GENERAL-ly speaking...

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-14 : 05:36:11

This is my nightmare. Really busy site with thousands of users paying for a 6 month subscription. Only a few days down the line we notice that everyone has been given a 6 day subscription because SQL had flipped the dates around. That's why I'm considering splitting it up. I know you all think I'm mad, but hey we stood alone in 1939, we invented radar, the jet engine, Royalty, soldiers with funny hats..
Jack

DGMelkin
Starting Member

24 Posts

Posted - 2002-02-14 : 08:35:37

quote:
but hey we stood alone in 1939, we invented radar, the jet engine, Royalty, soldiers with funny hats..

Wow, you guys invented Royalty in 1939? I guess I really did waste my college years studying British history . . .

In terms of dates, what about storing them as floats/numerics? We've been playing with similar issues here, trying to make sure that our program can work in the UK as well as the US. Do you think that's a viable option for a solution?

-D

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-14 : 09:22:15

At last someone who doen't think I've gone loopy! How would you propose to use dates as floats/numeric? BTW I'm using the split dates - i.e. datepart(day,getdate()) - as tinyints

Jack

P.S. Oh yeah, we invented Royalty in 1939 (October 24th to be exact) as a morale boosting exercise. All 'Royalty' prior to that date is actually a manifestation of False Memory Syndrome.

DGMelkin
Starting Member

24 Posts

Posted - 2002-02-14 : 10:31:21

I agree with everyone else that you shouldn't split it into different columns. But if you convert the datetime into a numeric(28,14) and store it that way, if you convert that number back to datetime, as long as whatever you're using to return the value is using the regional settings, it should be displayed in the proper format.

So, when I run this :

SELECT convert(datetime, 37299.43385277778000)

under the US regional settings in Query Analyzer (with the option to use regional settings checked), I get: 2/14/2002 10:24:44 AM
When run under UK settings, I get: 14/02/2002 10:24:44

-D

PS. And I'll mark Oct. 24th on my calendar now . . .

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-14 : 11:03:15

Have I not chased my own tail then, however, as I do not trust the regional settings? If those setting are wrong and nobody has noticed then I'm still stuffed..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-14 : 11:21:37

Let's try another angle on this:

1) How many date values need to be entered?
2) Are they all entered manually or are some calculated? Which ones are manual and which are calculated?

Don't spare any detail Jack, put 'em all out there for us. I'm looking to see if there's a way to eliminate all manual date entry. In doing so, I'm pretty sure all of the date values can be generated on SQL Server, and then regional settings are not a problem anymore.

And I know you don't want to hear this, but I can't help thinking that splitting them up is like trying to cure a brain tumor with aspirin: the headache may be gone, but the problem is still there. If you're already keeping them in separate columns, and STILL have invalid date values, then it's not solving the problem.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-14 : 11:48:52

OK. I'm not storing dates in seperate columns at the moment. Dates and times, manually entered and auto generated are stored in a single datetime column as any sane person would do. However, since the problems we've had with the regional settings I've been planning the mad, bad and dangerous split datetime idea.
Here's the scenario; a website has news items with active_from and active_to values stored in two datetime columns. The stored procedure selects news items where active_from is less than getdate() and active_to is greater than getdate(). Behind the scenes the site administrator adds news items and gives them active_to and active_from dates. All pretty basic so far. However surpise, surprise when news items that should be active do not appear on the site and those that shouldn't do. So we double check that the admin system is entering the correct dates and it is. We check the stored procedure and the code on the page and its fine. But, surprise , surprise SQL Server has interpreted datetimes inputted as UK format as US format. 01/03/2002 inputted becomes 03/01/2002 in the table. So we do all the workarounds, change the registry, make sure SQL Server is running UK locale and it works OK again until the next time.. My main problem here is that nobody will notice until its too late and you have a load of bad dates. Even worse, imagine it with a large number of users updating a table.. Am I making sense now??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-14 : 12:26:54

OK, have you tried formatting and passing the date values as yyyymmdd? This should be simple to do on the web side, you can have three input boxes or dropdowns for month, day, and year, and combine the values into a yyyymmdd format and shoot it to SQL Server. Anything that DOES NOT allow someone to enter the entire date manually.

Problem solved? If this doesn't work, then there's a MAJOR problem with your system.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources