Link to home
Start Free TrialLog in
Avatar of jensjakobsen
jensjakobsenFlag for Denmark

asked on

SQL and ASP - date comparison

I have posed this  question before - unfortunately I need to do it again.

I have a date stored in my SQL server. It's stored as YYYY-MM-DD.

I need to compare that date with the current date - which in ASP is date().

When I do a <%=date()%> I get the date in this format MM/DD/YYYY.

I need to compare both date within a ASP sentence like:

<%IF date() > rsMydate(dmyStartDate) THEN
..
..
..
END IF
%>
 How do I do that?
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Look into the use of FORMATDATETIME..

http://www.w3schools.com/vbscript/func_formatdatetime.asp

HTH,

Kent
Avatar of jensjakobsen

ASKER

Hi Kent

I need a little more help than a link - I am not quite the expert :)

Also I need both dates to be in the format: DD-MM-YYYY when I display them on my web pages.
If you look at the link you were given, you will see that your preferred format is not supported.  You will have to get the dates in string format and use 'substr' to extract and rearrange the dates to appear as you want them.
RealDate = CDate(rsMydate(dmyStartDate))
MySQLDate = Year(RealDate) & "-" & Month(RealDate) & "-" & Day(RealDate)
DisplayDate = Day(RealDate) & "-" & Month(RealDate) & "-" & Year(RealDate)

Any of this any use?

GH
Why compare strings at all?

<%IF date() > Convert.ToDateTime(rsMydate(dmyStartDate)) THEN
..
..
..
END IF
%>

Open in new window

lluden is on the right track, unfortunately that solution is for .net.

for classic asp, you can just do:

date1 = rsMydate(dmyStartDate)
date2 = date()

If DateDiff("d", date1, date2) > 1 Then
    response.write "date1 is before date2"
Else
    response.write "date2 is before date1"
End If

more info on the dateDiff function: http://www.w3schools.com/vbscript/func_datediff.asp
I think we need a little more information from the OP...

There seems to be two issues at play here:

1. The comparison of the dates
2. The display of the dates

To compare dates, we need to ensure that both dates are in a proper date format (which they may not be coming from a RecordSet).

To display a date in a particular format, we can either "formatDate" or manually design some output style, if a particular format is not available. This should also be protected with "IsDate", so that non-dates to do cause an error.

With this in mind, maybe the OP can describe exactly what they are trying to achieve, so that the Experts can recommend  a proper course of action...

GH
Hi all.

Thanks a lot for the feedback.

As stated - I need to be precise with my requests:

I use classic ASP and MS SQL 2008.

When I display dates from my SQL DB they are displayed as YYYY-MM-DD.

If I do a <%=date()%> it's displayed as MM/DD/YYYY.

I want to display all dates as DD-MM-YYYY.

I want to do a date comparison where both dates are DD-MM-YYYY (Danish format).

If I am not clear please let me know.

AND - it's not because I'm lazy, but I need a lot more help than a link - I need the whole shebang: Example, syntax and stuff.
And a comment more.

I have created a table where I have stored some seasons.

A season is defined with an ID, STARTDATE and a ENDDATE.

The DB fields are:
pkIntSeasonID, startdate, enddate,

An example could be:
1, 01-09-2011, 31-08-2012
2, 01-09-2012, 31-08-2013
3, 01-09-2013, 31-08-2014
4, .....
5, .....
..

If the current date is 16-04-2013 (DD-MM-YYYY) I want to do display the matching pkIntSeasonID, and store it as a session value - in this example the result would be 2.
for displaying dates in the DD-MM-YYYY format, you can use the convert() function directly in sql to get the format you want:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
from http://www.sql-server-helper.com/tips/date-formats.aspx

to compare the dates, you can use the code I previously posted, using the dateDiff() function
I think The_Big_Daddy has it right for DB Dates, but being highly critical...

The OP Stated "Display" with dashes, rather than slashes. So:
<%=Date()%>
Becomes:
<%=Day(Date()) & "-" & Month(Date()) & "-" & Year(Date()) %>

This should probably be put in a function, so that all dates can be written to screen in the same way:

Function ShowDanishDate(Input)
    ShowDanishDate = Day(Input) & "-" & Month(Input) & "-" & Year(Input)
End Function

then use:

response.write ShowDanishDate(DateInAnyDateFormatYouLike)

GH
According to the link video posted the convert function will use dashes
@ GH, post ID: 38578169.

The statement:
<%
Function ShowDanishDate(Input)
    ShowDanishDate = Day(Input) & "-" & Month(Input) & "-" & Year(Input)
End Function

response.write ShowDanishDate(DateInAnyDateFormatYouLike)
%>

Open in new window


displays the date 30-12-1899.
Oh sorry.

I corrected it to:

<%
Function ShowDanishDate(Input)
    'ShowDanishDate = Day(Input) & "-" & Month(Input) & "-" & Year(Input)
	ShowDanishDate = Day(Date()) & "-" & Month(Date()) & "-" & Year(Date())
End Function

response.write ShowDanishDate(DateInAnyDateFormatYouLike)
%>

Open in new window


Now I got <%=date()%> in the box - it's displayed as DD-MM-YYYY as I needed it to be.

How do I then get my date from the DB to be displayed in the same format? It's currently in the format of YYYY-MM-DD.
And please remember that I need to compare the finished dates againts each other.
Did you try the convert function like I suggested
Hi The_Big_Daddy

Yes it worked fine.
Do you mean that I can compare your converted date with the solution from G_H?
for the actual comparison (such as an if statement) you don't need to change the format from  slashes to dashes, you can just do the comparison, especially if you use the dateDiff code I posted previously. if you want to also display the current date using date(), you don't need to even use a custom function, since all you want to do is change the slashes to dashes, you can do:

todaysDate = Replace( date, "/", "-" )

maybe if you explain exactly what your end goal is, we experts can help piece together the final bit of code you need for everything
OK - I can do that. And thank you for your patience.

My END GOAL is to save a record ID into a session variable.

The record ID belongs to a recordset where the fields are:
pkIntSeasonID, startdate, enddate

I have created several recordsets like:
1; 01-09-2011; 31-08-2012
2; 01-09-2012; 31-08-2013
3; 01-09-2013; 31-08-2014
4: .....
5; .....
..

I want to create some sort of function that will identify WHICH season I'm in.

As you can see from my recordsets I have a "startdate" and an "enddate".

If today's date is BETWEEN the "startdate" and the "enddate" then I have identifyed my season and I then want to store the ID (pkIntSeasonID) as a session variable.

I hope that I have explained my self well enough to be understood - I'll happily elaborate if needed.
can you post the sql you're using? we should be able to add a column to the select statement to determine the current season
ASKER CERTIFIED SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have no SQL - I need help with that.
Thanks, but can't do that kind of SQL sentences :(
I am so embarrassed....

SELECT *
FROM dbo.tbl05Season
WHERE getdate() between dmyDateStart and dmyDateEnd

Thanks Big Daddy for showing me the way!