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.

Date Formula Problem

I am a complete noob with crystal reports and am in real trouble with monthly billing, so any help would be greatly appreciated.
our billing period is from 7/31 - 8/30, and the reports are run on 8/31. We found a bunch of mistakes and had them fixed but now I need to be able to rerun
the report for that time period today. All my time formulas are below, please help.

Report Date

"For the Month of " & ToText(CurrentDate - 15, "MMMM") & " " & ToText(CurrentDate - 365, "yyyy") & " & " & ToText(CurrentDate - 25, "yyyy")

Current Year

If {orders.DATE} in Maximum(LastFullMonth) to CurrentDate - 1 then 1

Previous Year

If {orders.DATE} in Maximum(LastFullMonth) - 365 to CurrentDate - 366 then 1That's the problem with using CurrentDate - it only works on the actual date!
Replace the use of CurrentDate with a formula. Make the formula return the date you want to simulate running for. Then you'll only ever need to change one thing in the report to get it to run for whatever date you like.
Or take it one step further and use a parameter so you don't even have to change the report. You could still use an intermediate formula so that you can make it work however you want without having to alter the report much at all.|||How would I go about setting it up to just use a specified date range, I tried using DateValue and for some reason it would always come up with the year 1898.

Date Format in SQL

I have a question and any help would be greatly appreciated. I'm failry new to MS SQL, though i have been doing Access for sometime and am familiar with its SQL. I'm updating a database to MSSQL and am creating some views off of some old queries but the syntax has changed. Here is the Access SQL:

PARAMETERS Month Short;
SELECT tblrec.HCNumber, tblrec.LocationID, Sum(tblrec.Recvalue) AS RecValue, tblrec.use, tblrec.Unit, Format([Date],"m") AS Month
FROM tblrec
GROUP BY tblrec.HCNumber, tblrec.LocationID, tblrec.use, tblrec.Unit, Format([Date],"m")
HAVING (((Format([Date],"m"))=Month([month])));

The portion that I am having a problem with is the FORMAT statement to change the date column so it only displays the month (1-12) in the result pane. Any help would be greatly appreciated.

THanks!!format([date], "m") = datepart(month, current_timestamp)|||datepart(month, [date])

Be very careful when using keywords as names for objects in sql - I strongly suggest not using keywords.