Date/Time Format Help! - 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

Other Forums

MS Access

Date/Time Format Help!

Author

Topic

kourvoisier
Starting Member

4 Posts

Posted - 2005-10-18 : 14:39:50

My webpage uses asp(vbscript) and access database to store info.

I have a field in my access database that is of type date/time
(short date - to be exact) ex.(10/08/2005)

I perform a sql query in vbscript to the access database:

SELECT *
FROM tblMain
WHERE fldDate = '10/08/2005'
ORDER BY fldCompanyName ASC

i get no results and a 500error when this query is performed.

The problem i think is the '10/08/2005' is text
so when the access database looks in the fldDate field its looking for the date/time format so it never finds a match instead sends back an error.

How can i format the sql query to be in the right format for access database date/time format

Help!

Thanks in advance

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-18 : 14:44:23

Try:


SELECT *
FROM tblMain
WHERE fldDate = #10/08/2005#
ORDER BY fldCompanyName ASC

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

kourvoisier
Starting Member

4 Posts

Posted - 2005-10-18 : 17:44:16

seventhnight,

really appreciate it....
that solved the problem. believe it or not i was sitting for hours trying to figure it out.

I thought i had to use some converting functions or something.(lol)

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:41:56

In Access you need to use # instead of single quote for dates. Similarly when you use Like you need to use * instead of %

Madhivanan

Failing to plan is Planning to fail

natg504
Starting Member

14 Posts

Posted - 2005-10-19 : 16:11:35

I'm also working with ASP and an Access database. I would like to sort a query by date, but the Access database has that field defined as text, so some dates don't sort correctly. (10/1/2005 comes before 9/1/2005). Is there a way that I can convert the date field to a DATETIME datatype when I select it so that it sorts correctly?

Thanks,

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-19 : 16:16:09

this may do it...:

Select ... Order By cdate(yourDateField)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

natg504
Starting Member

14 Posts

Posted - 2005-10-19 : 16:49:18

That worked. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources