SET DATEFORMAT dmy - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SET DATEFORMAT dmy

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-31 : 19:14:30

I want to post a date and search a date

I posted 01/08/07 and it went into my database like this: 08/01/2007
then I posted 15/08/07 and it went in like: 15/08/07

I have put SET DATEFORMAT dmy in the INSERT INTO and SELECT SQL statements

now i get: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
/pubspace/events.asp, line 96

line 96:

SET DATEFORMAT dmy SELECT * FROM members INNER JOIN (pubs INNER JOIN tEvents ON pubs.PubID = tEvents.PubID) ON members.UserID = tEvents.UserID WHERE Start_Date = '" & dDate & "' ORDER BY eventTime "

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-31 : 19:42:12

1. use parameters. do not contencate SQL
2. if you can't (don't see why not), pass dates in yyyyMMdd or yyyy-MM-dd format.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-31 : 19:43:23

I'm not sure how SET DATEFORMAT dmy works, How about this?


SELECT * FROM members INNER JOIN tEvents ON
members.UserID = tEvents.UserID
INNER JOIN pubs ON tEvents.PubID = pubs.PubID
WHERE
CONVERT(VARCHAR(10),Start_Date,112) <= CONVERT(VARCHAR(10),'"& dDate & "',112) ORDER BY eventTime

Thanks
Karunakaran

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-31 : 20:00:42

that works thanks karuna but now i can't see my events?!

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-31 : 20:05:35

Event goes into database correctly now but does not show the following!


<%Do While NOT Rs.EOF%>
  <tr>
  	<td><a href="viewmember.asp?UserID=<% = Rs("UserID")%>" class="textareatext"><% = Trim(Rs("rsUser"))%></a></td>
  	<td><a href="viewpub.asp?PubID=<% = Rs("PubID")%>" class="textareatext"><% = Trim(Rs("rsPubName"))%></td>
  	<td><% = Trim(Rs("Event_Title"))%></td>
  	<td><% = Trim(Rs("eventTime"))%></td>
   </tr>
   <tr>
   	<td colspan="4"><% = Trim(Rs("Event_Details"))%></td>
   </tr>
   <tr>
   	<td colspan="4" class="midgreenline"></td>
   </tr>
   <%Rs.MoveNext
		Loop
%>

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-07-31 : 22:17:27

Can you post the output of the query results?

Thanks
Karunakaran

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 04:13:26

I select a date in the calendar where an event has been posted and in teh URL query it says at the end: events.asp?date=15%2F08%2F2007

so my SELECT statement WHERE Start_Date = dDate should work I mean it should show some results?!

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-01 : 11:32:31

Execute the query in Query Analyzer and see if you get any result for the date parameter you are passing. If you get results in QA and records are not displayed in the web page then there is something wrong in the asp code.

Thanks
Karunakaran

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 11:59:21

sorry but I am very new to all this, how do i do this?!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-01 : 12:15:37

As Mladen wrote:

1. use parameters. do not contencate SQL

Never, never concatenate together sql strings like that. ALWAYS simply use parameters. Then you are passing VALUES directly to the database and formats do not matter. It also is shorter, quicker, easier and safer.

- Jeff
http://weblogs.sqlteam.com/JeffS

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-01 : 12:16:45

Replace the dDate with the date value you are passing.
[Code]
SELECT * FROM members INNER JOIN tEvents ON
members.UserID = tEvents.UserID
INNER JOIN pubs ON tEvents.PubID = pubs.PubID
WHERE
CONVERT(VARCHAR(10),Start_Date,112) <= CONVERT(VARCHAR(10),'15/08/07',112) ORDER BY eventTime
[/code]

Thanks
Karunakaran

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 12:37:26

i just tried it, it's not that! I need dDate in there as it has other code for teh calendar
I will post the calendar code where it says about dDate

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 12:42:13

at the top of teh page i sthis bit of code:

If IsEmpty(Request.QueryString("Date")) OR NOT IsDate(Request.QueryString("Date")) Then
		dDate = Date
	Else
		dDate = CDate(Request.QueryString("Date"))
	End If

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-01 : 12:58:03

If there is no records for the date you are passing (in this case, 15/08/07) you will not see any data in the web page.
First, find out if there is any data returned by your query for the date specified.

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 13:10:23

there are records it worked before I think its the WHERE part of the string?!

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-01 : 13:44:32

quote:
Originally posted by jarv

at the top of teh page i sthis bit of code:
If IsEmpty(Request.QueryString("Date")) OR NOT IsDate(Request.QueryString("Date")) Then
dDate = Date
Else
dDate = CDate(Request.QueryString("Date"))
End If

Find out what is the value dDate has after this code. Did you try debugging to see what value dDate has? else try adding Response.write(dDate) to see what value it shows in the webpage.

jarv
Posting Yak Master

131 Posts

Posted - 2007-08-01 : 14:10:34

that's it!!!

it returns: 8/15/2007

it needs to be 15/08/2007 hmm now how to do that?!

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-08-01 : 15:04:34

quote:
Originally posted by jarv

that's it!!!

it returns: 8/15/2007

it needs to be 15/08/2007 hmm now how to do that?!


If IsEmpty(Request.QueryString("Date")) OR NOT IsDate(Request.QueryString("Date")) Then
		dDate = Date
	Else
		dDate = CDate(Request.QueryString("Date"))
	End If

dDate = Year(dDate)

If(Month(dDate)) < 10 Then
dDate = dDate&"-"&"0"+Month(dDate)
Else
dDate = dDate&"-"&Month(dDate)
End If

If(Day(dDate)) < 10 Then
dDate = dDate&"-"&"0"+Day(dDate)
Else
dDate = dDate&"-"&Day(dDate)
End If