Date issue

[44nato44]

Hi.

I need to get a date put into my db, but I have a few issues.

The date above my sql string is my check to see if I add the correct year and it says 2009, which is correct. I am trying to put in the date 21 Feb. 2009 (21/02/09)

Below is my sql string and everything looks fine to me. But when I need to write my date out it says 9-2-2021 (day-9-Month-2-Year-2021)

2009-21-02-09
2009-21-02-09

UPDATE tblTaskTool SET Project = 'MLLP', Tool = 'Strategy Series', Priority = 'High', Task = ' ', Status = 'Assigned', Owner = 'nato', HoursSpent = 0 , Requested = #21-02-09# , Started = #21-02-09# , Estimated = #21-02-09# , Completed = #21-02-09# , Comments = ' ' WHERE TaskID = 25


Anybody has any ideas ?

 

[gmmastros]

The database is interpreting your format as yy-mm-dd. Instead of fighting it, try 'going along' with it. If you format your date the same way that Access is expecting it, you shouldn't have any problems.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[44nato44]

in the db it is dd/mm/yy.. its set to short date format

Its an access db

 

[vicvirk]

convert the date using CDATE and place that value into a variable...pass it in as mm/dd/yyyy regardless of how you have it set up in access...

dteToInsert = cdate(dteToConvert)
sql = "update myTable set date_field = #" & dteToInsert & "# where myCondition = myCriteria"

TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[nomorestress]

Dates are a problem with all databases and web sites.
IIS handles dates according to its own country setup
The following scripts may help

' ---------------------------------------------------------------------------------------------------
' ASP/VB code to validate date in and out of sqlserver


dim iisdate
isdate=0 '=1 us =0 uk

' ----------------------------------------------------------------------------------------------------
' return a correctly formatted date
' if m>0 then return date and time
function getdate(d,m)
dim i
dim s
dim d1
dim d2
dim d3
s=d

if isdate(d) then
i=instr(s,"/")
if i>0 then
d1=right("00" & left(s,i-1),2)
s=mid(s,i+1)
i=instr(s,"/")
if i>0 then
d2=right("00" & left(s,i-1),2)
s=mid(s,i+1)
if(s>1900) then
i=s
else
i=s+2000
end if
d3=i
end if
end if
if iisdate=1 then
thedate=d2 & "/" & d1 & "/" & d3
else
thedate=d1 & "/" & d2 & "/" & d3
end if
if m=1 then
thedate=thedate & " " & right("00" & hour(d),2) & ":"
thedate=thedate & right("00" & minute(d),2)
end if
getdate=thedate
else
getdate="00/00/0000"
end if
end function

' ----------------------------------------------------------------------------------------------------
' convert a database date to sqlserver write format
function setdate(d)
dim s
if isdate(d) then
s=getdate(d,0)
' modify the line below to your required format
setdate=mid(s,7,4) & mid(s,4,2) & mid(s,1,2)
else
setdate="00000000"
end if

end function

' ----------------------------------------------------------------------------------------------------
' convert a text date to sqlserver write format eg. from an html field
function setdate2(d)
dim s
if isdate(d) then
if iisdate=1 then iisdate=0 else iisdate=1
s=getdate(d,0)
if iisdate=1 then iisdate=0 else iisdate=1
' modify the line below to your required format
setdate2=mid(s,7,4) & mid(s,4,2) & mid(s,1,2)
else
setdate2="00000000"
end if
end function








// -----------------------------------------------------------------------------

// Java script to validate date entry to dd/mm/yyyy

// ------------------------------------------------------------------------------
// FUNCTION GETDATEPART
// ------------------------------------------------------------------------------
// used in conjunction with function validatedate()
// returns first and subsequent tdate parts

function getdatepart(dt){
var i
var d=dt
var d1
i=d.indexOf('/')
if(i==-1){
return [d,-1]
}else{
d1=d.substr(0,i)
d=d.substr(i+1,(d.length-i))
return [d,d1]
}
}

// ------------------------------------------------------------------------------
// FUNCTION VALIDATEDATE
// ------------------------------------------------------------------------------
// returns a validated date string in the correct format fro the current iis country

function validatedate(e){
var darr=new Array()
var d=e.value
var i
var o
var b=true
var d1,d2,d3
var k=event.keyCode
if(d.length>0){
for(i=0;i<2;i++){
o=getdatepart(d)
d=o[0]
darr=o[1]
if(darr==-1){
b=false
alert('Not Valid Date')
e.value=""
return 1
}
}
if(b==true){
d1=darr[0]
d2=darr[1]
d3=parseInt(d)
if(d3<2000) d3=d3+2000
if(d1.length<2) d1='0'+d1
if(d2.length<2) d2='0'+d2
e.value=d1+'/'+d2+'/'+d3
return 0
}
}else{
return 0
}
}