Which sql-statement to get the date and time ?

[henryhandle]

I have an access table (make with access xp) ,
which has a colum name “datecolum”
in the access format like this : 12.12.2001 12:21:22
(date and hour)
First i read this :
Sql_s= Select datacolum from Customer
rs.open Sql_s, con
……………….
After that putout the result in Sql_s:
Set d= rs.Fields („datecolum“)
Response.Write ( FormatDateTime(d,ShortDateTime )
Output is:
Output : 12.12.2001
..........
11.11.2001
??
Response.Write ( d )
get the same result!!
What shall I do to get :12.12.2001 12:21:22
?

 

[CassidyHunt]

Try this:

Response.write FormatDateTime(d,0)

Let me know if it Works

Cassidy

 

[shaygan]

hi Cassidy
the Result of
FormatDateTime(d,0)
is the same : 12.12.2001


?
I think this is a read pocess problem,
maybe i need s special sql-Statement to
read this format, because this is a
strangely access format!!!!!???????????

 

[ChrisHirst]

Formatdatetime will only display either the date or the time

So

response.write formatdatetime(d,vbshortdate) & " " & formatdatetime(d,vblongtime)

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[ChrisHirst]

BTW There is no "shortdatetime" constant.

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[henryhandle]

i become now a output as like that:

12.12.2001 00:00:00
...................
12.12.2001 00:00:00
12.12.2001 00:00:00

!!??
in the database i have

about 40 different date valuse!!??
hier the sql -statenemt and the datecolum:
select datecolum where datecolum = # 12-12-2005#

12.12.2001 12:21:22
12.12.2001 12:22:06
12.12.2001 12:24:11
........
12.12.2001 12:26:44



????

 

[cheerio]

1. Do not cross post - that wastes people's time. This is not an Access issue.

2. Please paste original code rather than retyping - your code above has mismatched brackets. In addition it does not show the customer so it is unclear how you know which rows are being fetched. Asking us to debug your inaccurately retyped code is an insult.

3. You should not need SET before an assignment unless you are assigning an object like a recordset

4. Look at the help for this function eg

http://msdn.microsoft.com/library/d...y/en-us/script56/html/vsfctformatdatetime.asp

Note this was the first link Google offered me on a search for FormatDateTime

5. Do not use magic numbers in your code like 0,1,2. That Microsoft link shows named values like vbLongDate and vbGeneralDate which make it easier for everyone to see the real format you are using.

6. vbGeneralDate gives both date and time (this is the magic number zero mentioned above)

7. You should adopt a strategy of isolating problems. Here the issue could be Access, ADO, ASP /vbscript, the use of the FormatDateTime function etc.

8. So forget all your connection strings, Access databases etc. Write a simple script that tells you the date and time as per your computer's system clock. When you get that working you will have the basic asp and FormatDateTime function resolved. Only when that is working should you try connecting to a database. By breaking your problems down you have more focus and will ask the right question in the right place.

 

[shaygan]

thanks for all your hints!
you see how confuse i have been!
this is a code part:
strcon = "Driver={Microsoft Access Driver (*.mdb)};Dbq= dbpath;Uid=myid;Pwd=pw"
set con = CreateObject ("ADODB.connection")'// connection object
set rs = CreateObject ("ADODB.recordset")
con.open strcon
t_sql= "select * from customer where [Meantime] = #2005,02,10# "

rs.open t_sql, con
'***************************
while NOT rs.EOF

Response.Write( rs.Fields("ID") "<BR>" & vbCrLf )
set d=rs.Fields("Meantime")
Response.Write(FormatDateTime(d,vbLongDate) & " " & FormatDateTime(d,vbLongTime) "<BR>" & vbCrLf)


rs.MoveNext
wend 'just to test system date and time
Response.Write(" <<<< " & FormatDateTime( NOW(),vbGeneralDate) )




??

 

[cheerio]

I am surprised you get any data returned.

where [Meantime] = #2005,02,10#

selects where the date/time is exactly 10 Feb 2005 ie no hours minutes or seconds are permitted. So at best you are selecting data with no hours minutes or seconds in which case your ASP code is always going to report times with no hours minutes or seconds.

If you want the possibility of selecting times during the day you need one of the following:

where [Meantime] >= #2005,02,10# and [Meantime] < #2005,02,11#

or

where [Meantime] between #2005,02,10# and #2005,02,10 23:59:59.99#

 

[henryhandle]

!!!!!!!

the Problem was because of > in sql -statement,
that cheerio
said !
thanks a lot for
your help