SQL Date/Time 1

[dvannoy]

I have a field on my SQL server thats a date/time stamp..When opening a form I prompt using a input box. I want the user to enter the date in which they want to view the data. How can I make VB ignore the time and just look at the date entered in the inputbox..

Dim sName As String

On Error GoTo Error_Handler

Screen.MousePointer = vbHourglass

sName = Trim(InputBox$("Enter Date Below", "Search for Data&quot) '& "%"

strRs = "SELECT "Fields WHERE DateTimeStamp LIKE '%" & sName & "%'"

Set rs = New ADODB.Recordset

rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Open strRs, strCn

Any help would be appreciated

Thanks

 

[poltergeist]

sname = cdate(Input$("enter date below","Search for date&quot)
' i don't know it's right but I use the # for the % char
strRs = "SELECT "Fields WHERE DateTimeStamp LIKE '#" & sName & "#'"

peterguhl@yahoo.de

 

[vickram]

did u try something like this ?..i mean the format() function
it should be MM/dd/yyyy

strRs = "SELECT Fields WHERE DateTimeStamp LIKE '#" & Format(" & sName & ", 'MM/dd/yyyy')#'"

vickram

 

[johnwm]

Only use # signs round dates in Access (Jet) databases.

Try something like:
myStart = cdate(sname & " 00:00:01&quot
myEnd = cdate(sname & " 23:59:59&quot
strRs = "SELECT myFields From myTable WHERE DateTimeStamp BETWEEN " & mystart & " AND " & myEnd

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[chiph]

vickram -
i mean the format() function it should be MM/dd/yyyy

Only if you're in the US. Europe uses DD/MM/YY style, which confuses the heck out of the database (It can't tell January 7th from July 1st). A better way to format dates is to use ISO-8601 (it's an Internet RFC too, but I forget which one), which the database has no problems with: YYYY-MM-DD Hh:Nn:Ss. This is the Japanses standard, too. It has the advantage (other than being unambiguous to the database) of naturally sorting when displayed.

Chip H.



If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Norris68]

Following up on chiph, I have a FAQ at

http://www.tek-tips.com/faqs.cfm?spid=958&sfid=4015

that details the ISO format, including a function to convert dates into ISO format. The formats include options for date/time, date-only & time-only. The format you would use for date-only is:

{d 'yyyy-mm-dd'}
e.g. today would be {d '2003-09-01'}

 

[1127]

use FormatDateTime function

 

[1127]

and in the SQL string pass trough de data as string.

 

[dvannoy]

johnwm,
I tried your code and I keep getting "Incorrect Syntax near '12'..

any ideas??

Thanks

 

[MarkSweetland]

If a single date is to be viewed, I think the easiest method to use (on SQL Server) would be datediff:

if isDate(sName) then 
  strRs = "SELECT Fields WHERE datediff(day, DateTimeStamp, '" & sName & "') = 0"
end if

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[dvannoy]

Thanks!! worked perfect

 

[dvannoy]

MarkSweetland,

how can I use the Bewteen staement with your code? I am prompting with two inputboxes to enter a starting and ending date to get the records. everywhere I have put the Between I get errors...

Thanks

 

[MarkSweetland]

A little slower, but you can use convert()


SELECT Fields
WHERE convert(varchar, DateTimeStamp, 1) between '" & BeginDate & "' and '" & EndDate & "'"


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach