SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    whats the correct date format for db?

    I'm hooked up to an access database and I want to input the date and time into a field that's set up as 'date/time' but when I try to input this (now) into that field I get an error like this.
    Data type mismatch in criteria expression.

    any suggestions?

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am assuming you are using an ASP page

    http://www.sitepoint.com/forums/show....php?p=1287262

  3. #3
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, I am using asp, but isnt there a date format that fits into the database if its set up as date/time. I used to have it set up as text and it accepted this. but when I change the type of data that the database takes in the date_time_opened column from text to date it no longer accepts it.

    "INSERT INTO hotlines(date_time_opened) VALUES('" & (now) & "')"

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That's because you're not using the right format for access:

    "INSERT INTO hotlines(date_time_opened) VALUES(#" & (now) & "#)"

    You COULD also just use:
    "INSERT INTO hotlines(date_time_opened) VALUES(now())"
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so, the only diffence I really see from mine and the second one is that 'now()' has changed to now(). Am I right? Cause when I get rid of the single quotes I get this error.
    Syntax error (missing operator) in query expression '3/17/2005 8:47:26 AM'.

  6. #6
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I response.write my sql statement and it looks like so.
    INSERT INTO hotlines(date_time_opened) VALUES(#3/17/2005 4:02:05 PM#)
    and I still get this error
    Data type mismatch in criteria expression.
    I dont know what I'm doing wrong here.

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That is bizarre. If you're using access, that should work, unless you've specified a format perhaps?

    And so do you have this for your SQL?
    "INSERT INTO hotlines(date_time_opened) VALUES(#" & (now) & "#)"

    or this?
    "INSERT INTO hotlines(date_time_opened) VALUES(now())"
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Your positive the field is date/time?

  9. #9
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe it's a locale thing?

    What country are you in?

    What locale setting is Windows using?

  10. #10
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Local settings is done via

    e.g <%=session.lcid = 1033%>

    I use 1033.

  11. #11
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell
    That is bizarre. If you're using access, that should work, unless you've specified a format perhaps?

    And so do you have this for your SQL?
    "INSERT INTO hotlines(date_time_opened) VALUES(#" & (now) & "#)"

    or this?
    "INSERT INTO hotlines(date_time_opened) VALUES(now())"
    I was using the top one. what do you mean by specified a format? I've specified the date/time format and it doesnt work, but when I specify the text format it does. I want the date/time to work

  12. #12
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Thing
    Your positive the field is date/time?
    yes, I double checked and it is date/time

  13. #13
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jimfraser
    Maybe it's a locale thing?

    What country are you in?

    What locale setting is Windows using?
    I believe its using central standard time if that means anything.

  14. #14
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're in the US it shouldn't be a locale problem. I was thinking maybe IL stood for Israel.

  15. #15
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, hahahaa. think it will accept the date if I get rid of /'s? and the pm or something?

  16. #16
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Universal format

    Whatever is the locale or setting at server (IIS + SQL), using a common date format is always good.

    Use "yyyy-mm-dd" while inserying into the database. It will be stored in the exact format as you want.

    Regds,
    Avinash

  17. #17
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, the thing is.... I have this format in my 'access' database. I've gone on access forums and found that others have found this too. I set up my format as this mm/dd/yyyy hh:nn:ss AM/PM and you would think it would give me double digits all around (except for the year of course) well it gives me doubles for the mm and for the dd and for the nn and for the ss just not for the hh and I can't figure out why and its completely driving me crazy. Any suggestions anyone?

  18. #18
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got sick of dealing with date formats because I couldn't rely on the fact that my test server and my production server used the same default locale, or the SQL server was in Spanish, or stuff like that.

    I write my dates to the DB in a char(14) field as such:
    YYYYMMDDHHMMSS

    Here are 3 key asp functions I use:

    Code:
    Function Date2Datestamp(dDate)
    	Date2DateStamp = ZeroPad(Year(dDate),4) & _
    		ZeroPad(Month(dDate),2) & _
    		ZeroPad(Day(dDate),2) & _
    		ZeroPad(Hour(dDate),2) & _
    		ZeroPad(Minute(dDate),2) & _
    		ZeroPad(Second(dDate),2)
    End Function
    
    Function Datestamp2Date(vStamp)
    	sStamp = CStr(vStamp)
    	Datestamp2Date = DateSerial(CLng(Left(sStamp,4)), CLng(Mid(sStamp,5,2)), CLng(Mid(sStamp,7,2))) + TimeSerial(CLng(Mid(sStamp,9,2)), CLng(Mid(sStamp,11,2)), CLng(Mid(sStamp,13,2)))
    End Function
    
    Function ZeroPad(vThing, nLen)
    	sThing = CStr(vThing)
    	Do While Len(sThing) < nLen
    		sThing = "0" & sThing
    	Loop
    	ZeroPad = sThing
    End Function

  19. #19
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how are you setting up your "format"? Do you have it set at date/time and chose general date as the format? If so try another inserting technique w/ the addnew ...no need to worry about ##...not as efficient as sql insert/udate but just trying to get that mismatch to go way.

    Code:
    <% 
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRS = Server.CreateObject("ADODB.Recordset")
    FilePath = Server.MapPath("yourtablename.mdb")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
    
    strSQL="SELECT date_time_opened FROM hotlines;"
    objRS.Open strSQL, objConn, 3, 3
    
    objRS.AddNew
    objRS("date_time_opened ") = Now()
    objRS.Update
    
    objRS.Close
    SET objRS = Nothing
    
    objConn.Close
    SET objConn = Nothing
    %>

  20. #20
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jimfraser
    I got sick of dealing with date formats because I couldn't rely on the fact that my test server and my production server used the same default locale, or the SQL server was in Spanish, or stuff like that.

    I write my dates to the DB in a char(14) field as such:
    YYYYMMDDHHMMSS

    Here are 3 key asp functions I use:
    Have you ever done the 14 in an access database Jim? If so, I have this problem. I set up the date time format like so YYYYMMDDHHNNSS but instead it actually formats like this yyyymmddhnnss. In other words if my time is 2pm it will store the hour as 14 but if its 2 am it stores the hour like 2 not 02. so, no I have two fields that look like so.

    1st one looks like
    20050331140444 = 2pm
    and the 2nd one looks like so
    2005033120444 = 2am

    I think this may cause problems in the long run because it seems to move everything over a decimal place. I dont know what else to do in access though. The microsoft helpless file says that if you do double digits like hh it will store them in double digits and it does for all except for the hour. the month is fine, if its march it puts 03 as you can see, the day, the min, and even the second works fine, but the hour doesnt want to cooperate and I'm pulling my hair out trying to figure this out. LOL I'm at a loss.

  21. #21
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just use a char/string field in the DB, and ignore the various date/time types there are to choose from.

    All my conversion comes via asp - so if I do
    INSERT INTO MyDb (TimeStamp) VALUES('20050331023000')
    it stays that way

  22. #22
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    IL
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahh, cause its not date/time. I dont remember seeing a char(14) in Access. There's a number with integer, but that's about it (to the best of my knowledge anyway) and if it keeps the zero in the hour than it will subtract fine when I do a search so many days back.

  23. #23
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just make it a text field

  24. #24
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if as text...no way of searxhing records between dates...this could be extremely valuable for reports

  25. #25
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in a text field you can still do greater than/less than comparisons

    "20040101000000" < "20040101120000" < "20050202020202"

    and sorting works well too


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •