|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
SQL Server Dates
When you create a table of the 'datetime' type - what is the standard formatting it is expecting (ie dd/mm/yyyy) ?
Cheers Kip |
|
#2
|
||||
|
||||
|
the default is the computers system settings.
|
|
#3
|
|||
|
|||
|
Okay - is there anyway to set what format you give it?
Cheers Kip |
|
#4
|
||||
|
||||
|
No, regardless of what date you pass it, it will stored it in a particular format.
When retreiving it from the database, you can format it the way you want to display it. What exactly are you wanting to do? |
|
#5
|
|||
|
|||
|
Well - I want to write a routine whereby after a certain amount of time a news story is set to 'expire' so its no longer shown on the site, but an archive flag is set. No idea where to start really :s
Kip |
|
#6
|
||||
|
||||
|
Are you wanting to return the ExpirationDate of an article and if today equals the ExpirationDate, then set an InActive or DontShow flag?
|
|
#7
|
|||
|
|||
|
Yur nearly - I have the date the story was added and after a period of time.. say 3 months the Dontshow flag needs to be set.
Not sure wether to do this by form of a script that is run daily, or when theasp page is viewed it does it Is that Ozzy in your avatar BTW? ![]() Kip Last edited by Kip : May 24th, 2004 at 10:39 AM. |
|
#8
|
||||
|
||||
|
You can put the code on the asp page and it will run everytime the page is loaded.
Just do something like this Code:
Create the connection object
Set Conn = Server.CreateObject("ADODB.Connection")
Open the connection
Conn.Open "Your ConnectionString"
Create the sql statement to return the articleID and it's expiration date
strSql = "SELECT ArticleID, ExpirationDate FROM ArticlesTable"
Execute the sql statement
Set rs = Conn.Execute(strSql)
If Not rs.eof Then
do until rs.eof
newDate will equal the ExpirationDate plus 3 months
newDate = DateAdd("m", 3, rs("ExpirationDate"))
Compare ExpirationDate to the newDate
If rs("ExpirationDate") >= newDate Then
The Article is over 3 months old, so mark it expired
Conn.Execute("UPDATE ArticlesTable Set Expired = 1 or True WHERE ArticleID = " & rs("ArticleID"))
End If
rs.movenext
loop
End If
Conn.Close
Set Conn = Nothing
|
|
#9
|
|||
|
|||
|
Cheers mate - that looks pretty self explanitory
![]() I'm sure i'll be half decent at this one day. Thanks for the code example.. Very Kind of you.. Looking forward to testing this at work tommorrow! Kip |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > SQL Server Dates |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|