Mssql Formatting Date Problem 1

[kennedymr2]

I am having a problem formating an sql statement which is shown below.

When i run his sql i do not get any records.But i do have some records in the day in question.
I guess it is due to the xtime having a time on the end of it.??

xtime is a field in an mssql database as a datetime format

??How do i format the xtime in the sql string as format$(xtime,&quot;mm/dd/yyyy&quot in both the >= and the <=

Appreciate some help!!!

Wherex = &quot;Where xtime >= '&quot; & Format$(DTPicker1.Value, &quot;mm/dd/yyyy&quot & &quot;' &quot;
Wherex = Wherex & &quot; and xtime <= '&quot; & Format$(DTPicker2.Value, &quot;mm/dd/yyyy&quot & &quot;' &quot;
Wherex = Wherex & &quot; and cid >= &quot; & cf & &quot; and cid <= &quot; & ct

 

[vikram28]

Which database are u using.
I was having same sort of problem in MS-Access.
Try this out

sql = &quot;select * from tablename where date = format('&quot; & DTPicker1.value & &quot;','dd/mm/yyyy')&quot;

It worked for me .

Good luck
Vikram Shah

 

[CCLINT]

vikram28, that will not (always) work.
It depends on the machine's local settings for the date. If it is not set to US format (MM/dd/yyyy) and also set to the proper date seperator ( / or -), or not set to ISO format (yyyy-MM-dd) then it will not work.

Use:
& Format$(DTPicker2.Value, &quot;MM\/dd\/yyyy&quot &

Or (recommended)
& Format$(DTPicker2.Value, &quot;yyyy-MM-dd&quot &

 

[kennedymr2]

I am more after the formatting of the database field xtime
I cannot get the format$(xtime,&quot;mm/dd/yyyy&quot
into the sql string.
I am accessing a mssql database not an access database

 

[CCLINT]

Then you didn't try my examples yet? Doesn't look like it.

Formating the database date is also a bad solution, unless you ALSO format the date being passed to it.

 

[vikram28]

Thanx CCLINT ,
I never thought about that . Thanks a lot for this tip.

Cheers
Vikram

 

[kennedymr2]

Appreciate the help being offered.
Have tried the formats above, but still get no records.
I agree that both dates have to be formatted.
I am having a problem including the xtime in the sql text,
with it being formatted. ie how do i include xtime formatted in the sql line.

ie
Wherex = &quot;Where xtime >= '&quot; &
need >>>>
Wherex = &quot;Where format$(xtime,&quot;mm/dd/yyyy&quot >= '&quot; &

Cannot get the &quot; and the ' right in the above.

 

[CCLINT]

Use:
sql = &quot;select * from tablename where [date] = '&quot; & Format$(DTPicker2.Value, &quot;MM\/dd\/yyyy HH:NN:SS&quot & &quot;'&quot;

Or (recommended)
sql = &quot;select * from tablename where [date] = '&quot; & Format$(DTPicker2.Value, &quot;yyyy-MM-dd HH:NN:SS&quot & &quot;'&quot;

Please not that if records are in the DB table with TIME, you will need to include the correct TIME or use a BETWEEN clause.

If you have a Date/Time of 09/22/2003 10:30:00 in the database, then the Format function w/o the time will search for a Date/Time of 09/22/2003 00:00:00, and naturally no records will be returned.

So, you need to use:

&quot;select * from tablename where [date] BETWEEN '&quot; & Format$(DTPicker2.Value, &quot;yyyy-MM-dd HH:NN:SS&quot & &quot;' AND '&quot; & Format$(DateAdd(&quot;s&quot;, -1, DTPicker2.Value + 1), &quot;yyyy-MM-dd HH:NN:SS&quot & &quot;'&quot;

in order to find any TIME with-in that date

 

[kennedymr2]

CClint
Thanks very much for the advice, and trouble you have taken.
I now can seem the light as to the problem.
This sql is a bad area of vb.!@!@!@ for me.
Much appreciated.

 

[CCLINT]

You're welcome!