Date Query problem

Hi All,

I have an interesting problem, and would greatly appreciate any help that you can offer.

I have an sql database which will list programms due to be shown for each day. One field in it is a datetime filed which I am using to store the date and start time of each programme.

When I try to query the database to show the programmes for a specific day I keep getting errors saying 'Syntax error converting datetime from character string.'

I tried using GetDate() in my SQL query (i.e. WHERE l.date = GetDate() ) but that didn't return any results as times are used in the database field also. Plus I'd like to be able to query the database for future dates at a later time also.

Many thanks in advance for your help.

Regards,

Rich

--------

My code is:-

Sub NextListings()

Dim strDateTimeStart,strDateTimeEnd
strDateTimeStart = DateTime.Now.ToString("dd/MM/yyyy") & "00:00:00"
strDateTimeEnd = DateTime.Now.ToString("dd/MM/yyyy") & "23:59:00"

Dim MyConnection As SQLConnection
MyConnection = New SQLConnection(ConfigurationSettings.AppSettings("ConnectionString"))

dim sql,sql2
sql="SELECT l.date, l.StartTime, l.Title, l.Text, c.CatTitle FROM LISTINGS_TV l INNER JOIN CATEGORIES_TV c ON l.Category = c.CatTVID WHERE l.date BETWEEN '"+strDateTimeStart+"' AND '"+strDateTimeEnd+"' ORDER BY l.StartTime"
Dim resultsDataSet as New DataSet()
Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(sql, myConnection)
myDataAdapter.Fill(resultsDataSet, "listings")
Content.DataSource = resultsDataSet
Content.DataBind()
End Sub

I got it sorted now thank, I used:-

Dim strDateTimeStart,strDateTimeEnd
strDateTimeStart = DateTime.Now.ToString("dd/MM/yyyy")
strDateTimeEnd = DateTime.Now.ToString("dd/MM/yyyy")

sql="SELECT l.date, l.StartTime, l.Title, l.Text, c.CatTitle FROM LISTINGS_TV l INNER JOIN CATEGORIES_TV c ON l.Category = c.CatTVID WHERE (l.date BETWEEN '"+strDateTimeStart+" 00:00' AND '"+strDateTimeEnd+" 23:59') ORDER BY l.StartTime"

Cheers,

Rich

|||

If dou'd like to use just the datepart you can use CONVERT(Getdate(),103) will give you date in the format dd/mm/yyyy. so you can change your query to :

sql="SELECT l.date, l.StartTime, l.Title, l.Text, c.CatTitle FROM LISTINGS_TV l INNER JOIN CATEGORIES_TV c ON l.Category = c.CatTVID WHERE (l.date =CONVERT(Getdate(),103) ) ORDER BY l.StartTime"

If you'd like to know the other formats check out Books on line for Convert > Cast and Convert.

Here's a Cut n Paste from BOL :

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126(***)	ISO8601	yyyy-mm-ddThh:mm:ss.mmm(no spaces)
-	130*	Hijri****	dd mon yyyy hh:mi:ss:mmmAM
-	131*	Hijri****	dd/mm/yy hh:mi:ss:mmmAM

|||

Brilliant, thanks for that, looks a little cleaner than my version.

Cheers,

Rich

|||Rich,
Please note that the DD/MM/YYYY date format is ambiguous -- it canchange meaning depending on locale. For instance, take: 02/01/2005. Depending on where you are in the world, this canmean either February 1, 2005, or January 2, 2005.
For this reason, the suggested date format when using SQL Server is theISO format, YYYYMMDD. This does not change meaning depending onlocale and is therefore always safe to use in your code. Irecommend that you use it instead.
I also recommend reading this article by Tibor Karaszi, which explains the datetime datatypes in extensive detail:
http://www.karaszi.com/SQLServer/info_datetime.asp
|||

Thanks for that Adam, that's interesting to know, as I wasn't aware of that, think I'll alter my code slightly to use the YYYYMMDD format.

I'd be lost without you guys. Thanks

Regards,

Rich

|||Hey I need something similiar to that but the Convert function isn't working like it should be.
First of all I get a syntax error using convert(getdate(), 111). I canonly get it to work using convert(varchar(10), getdate(), 111). Itried to use that on my query but it does not work. It results on anempty set.
SELECT * FROM Files
WHERE Date = Convert(varchar(10), getdate(), 111)
The Date field is a datetime field, that shows as dd/mm/yyyy eventhough I set it to yyyy/mm/dd every time I insert a new row. What I amdoing wrong?
Thank you!
Juliana
|||Juliana,
DATETIME has no inherent string format -- it's stored as binary. The string format is determined by whatever client you're using.