Livio.net » ASP Functions Library

Loading... Please wait.

Users online:

Friday, November 22, 2024 ««

Join the Mailing List

DATABASE: SQL_Date Function

Go to ASP Functions Library

Go to Database ASP Functions Category

SQL_Date Function: Prepare dates for SQL query.

Description:

SQL_Date Function prepares dates for SQL query.
It needs the DateDB Function.
Database type: MSAccess, MSSQL, MySQL, Oracle

Syntax:

formatteddate = SQL_Date("datevalue", "databasetype")

Details:

Arguments:

datevalue:
any date value e.g. 27/10/2006

databasetype:
MSAccess
MSSQL
MySQL
Oracle

Example:

<%
Dim a
a = SQL_Date("27/10/2006", "MSAccess")
'--- returns: #2006-10-27#

a = SQL_Date("27/10/2006", "MSSQL")
'--- returns: CONVERT(DATETIME, '20061027', 112)

a = SQL_Date("27/10/2006", "MySQL")
'--- returns: '2006-10-27'

a = SQL_Date("27/10/2006", "Oracle")
'--- returns: TO_DATE('2006-10-27', 'YYYY-MM-DD')
or
'--- returns: TO_DATE('2006-10-27 22:12:05', 'YYYY-MM-DD HH24:MI:SS')
%>

ASP Source Code:

<%
Public Function SQL_date(ByRef adExpression, ByRef DbType)
    '--- If Expression valid date
    If IsDate(adExpression) Then
        '--- Return Date
        If LCase(DbType) = "access" Then
            SQL_date = "#" & adExpression & "#" '--- Access Database
        Elseif LCase(DbType) = "mssql" Then
               SQL_date = "CONVERT(DATETIME, '" & Replace(DataDB(adExpression), "-", "") & "', 112)" '--- MSSQL Database
        Elseif LCase(DbType) = "oracle" Then
            If datepart("h", DataDB(adExpression)) <> 0 OR datepart("n", DataDB(adExpression)) <> 0 OR datepart("s", DataDB(adExpression)) <> 0 Then
                SQL_date = "TO_DATE('" & DataDB(adExpression) & "', 'YYYY-MM-DD HH24:MI:SS')" '--- Oracle Database
            Else
                SQL_date = "TO_DATE('" & DataDB(adExpression) & "', 'YYYY-MM-DD')" '--- Oracle Database
            End if
        Else
            SQL_date = "'" & adExpression & "'" ' SQL Database
        End if
    '--- Else Expression not valid date
    Else
        '--- Return NULL
        SQL_date = "NULL"
    End If '--- IsDate(adExpression)
End Function '--- SQL_date
%>

asp functions

Array (16) - Procedures useful for working with arrays.

Data Conversion (13) - Conversion procedures convert data from one format to another.

Database (19) - Procedures to access a database and work with it's contents.

Date/Time (17) - Procedures to deal with date and time.

Debugging (1) - Procedures to help in script debugging

Email (2) - Email related functions

File/Folder read/write (16) - Procedures to work with files or folders on the server.

HTML Retrieval and Parsing (4) - Procedures for retrieving html and parsing it.

Math (7) - Procedures that perform mathematical operations.

Number (16) - Procedures useful for numbers.

Other (9) - Miscellaneous procedures

Path (6) - Procedures for working with absolute and virtual paths.

String (38) - Functions to manipulate, alter and check strings.

Array (16) - Procedures useful for working with arrays.

ArrayAverage Function - Calculate the Average of Items in a Numeric Array

bIsInArray Function - Determine if a Value/Object exists in an any kind multidimensional array.

CombSort Function - Sorts an array alphabetically (A - Z) or numerically (low to high).

ExchangeSort Function - Sorts an array alphabetically (A - Z) or numerically (low to high).

HasDups Function - Indicates if an array has at least one duplicate value.

ItemArrayPos Function - Check to see if element exists and return the position in the array.

MaxValOfIntArray Function - Get The max value of an Int Array without sorting

MultiArraySort Function - Sorts multidimensional arrays by column either ascending or descending.

RandomArray Function - Randomizes an Array's content's order

RemDups Function - RemDups removes duplicates from an array and returns a new array with no duplicates.

RemoveItemFromArray Function - Remove an Item from an Array

RevArray Function - Reverses the order of an array.

SelectionSort Function - Sorts an array alphabetically (A - Z) or numerically (low to high).

SplitMulti Function - Split function variation that accepts multiple separators

SplitRegExp Function - Uses a regular expression to split a string into an array of elements.

StrToArray Function - Converts a string or integer to an array of characters/bytes.

Data Conversion (13) - Conversion procedures convert data from one format to another.

CBit Function - Work with SQL Server BIT fields? This function converts anything to a bit value (integer 1 or 0)

Decrypt Function - Changes an encrypted string into readable text.

DegC Function - Converts a Fahrenheit temperature into a Celsius temperature.

DegF Function - Converts a Celsius temperature into a Fahrenheit temperature.

DegK Function - Converts Celcius temperatures to Kelvin.

Encrypt Function - Encrypts a string.

HTMLDecode Function - Decodes an HTML encoded string.

IPAddressToNumber Function - Convert an IP address to IP number

MetricToStandard Function - Translates a Metric measure into a US Standard measure.

Nautical/Flight Functions - Ten functions performing nautical/Flight conversion calculations

NumericToRoman Function - Converts numbers to roman format.

ProperCase Function - Returns a string in proper case.

StandardToMetric Function - Translates a US Standard measure into a Metric measure.

Database (19) - Procedures to access a database and work with it's contents.

CompactDB Function - Compact an MS Access database.

DateDB Function - Prepare date to be stored into a database in ISO format.

EscapeApos Function - EscapeApos properly escapes varchar input for sql server as well as MS Access.

EscapeBit Function - Escapes a boolean field for sql statements (sql server).

EscapeDate Function - Escapes a variant date for sql server.

EscapeInt Function - Escapes a number for sql statements (sql server).

MkDatabase Function - Creates an empty Microsoft Access Database.

NoSQLInjection Function - Protection against an SQL Injection attempt.

Procedures Function - Returns an array containing the names of all procedures in a database.

RecordCount Function - Returns the record count returned by an SQL statement.

RecordCount2 Function - Returns the record count returned from a given database Table.

RecSet Function - Returns a two-dimensional array representing the resultant recordset of an SQL query.

SQL_Boolean Function - Prepare booleans for SQL query.

SQL_Currency Function - Prepare currency for SQL query.

SQL_Date Function - Prepare dates for SQL query.

SQL_Numeric Function - Prepare numerics for SQL.

SQL_String Function - Prepare strings for SQL

SqlExec Statement - Executes an sql statement that returns no records on a database.

Tables Function - Returns an array of all table names in a database.

Date/Time (17) - Procedures to deal with date and time.

CheckDayLightSavings Function - Check if a passed datetime value is in Daylight Savings Time.

ExaxtAge Function - Calculate a Person's age

GetDaysInMonth Function - How many days in a given month

GetFirstOfMonth Function - Returns a date variable containing the first day of the month submitted.

GetLastOfMonth Function - Returns the last day for a given date.

GetStandardChangeDate Function - Returns daylight saving change date (Last Sunday in October)

GetwWorkDays Funcion - Get work days between a interval

IsLeapYear Function - Function to determine if a given Year is a Leapyear

Military2TwelveHour Function - Convert military time to twelve hour format.

MilitaryTime Function - Formats a time measure into proper Military time (24 hr).

NthPreviousWeekdayDate Function - Find the previous specified day of week before the specified date.

NthWeekdayDate Function - Find the date of the specified day within the month.

SecondsToTime Function - Coverts seconds to hh:mm:ss format.

SwapDate Function - Changes an US into an Internationally formatted date (mm/dd/yy-dd/mm/yy) and vice-versa.

TimeInRange Function - Determine if the current Time falls within a range

TimeToSeconds Function - Converts hh:mm:ss to seconds.

WeekNumber Function - Retrieve The Week Number For a Given Date

Debugging (1) - Procedures to help in script debugging

Script_Variables Procedure - Debug your script variables collection

Email (2) - Email related functions

AsciiCode Function - Converts email address to ASCII Character codes. Stop spam bots/spiders.

Emailer Function - Send email using either CDOSYS, JMail, CDONTS or AspEmail components

File/Folder read/write (16) - Procedures to work with files or folders on the server.

DoExecute Function - Open an ASP file and execute its contents (instead of using an Include or Server.Execute()).

FileCopy Statement - Copies a file or folder.

FileCount Function - Count files in a given folder.

FileCreate Statement - Creates a file.

FileDateTime Function - Returns the time and date a file was last modified.

FileDelete Statement - Deletes one or more files.

FileFind Function - Checks for the existence of a file

FileRead Function - Read a text file.

FileRename Statement - Renames a file.

FileSize Function - Returns a long representing the size of a file in bytes.

FileWrite Statement - Writes to a text file.

FolderCount Function - Count sub folders in a given folder.

FolderCreate Statement - Creates a directory or folder.

FolderDelete Statement - Deletes a directory or folder.

FolderFind Function - Checks for the existence of a folder.

LastUpdated Function - Retrieve the last updated date of a page.

HTML Retrieval and Parsing (4) - Procedures for retrieving html and parsing it.

GetHrefs Function - GetHrefs Function parses a string for html anchor tags and returns them.

GetWebPage Function - Returns the html source code of a specified web address.

HighLight Function - HighLight keywords in a string.

ParseURL Function - Parses a portion of a URL.

Math (7) - Procedures that perform mathematical operations.

CalcArea Function - Calculates the area of a triangle, square, rectangle, etc...

CalcSphereSurface Function - Calculates the surface of a sphere

CalcSphereVolume Function - Calculates the volume of a sphere

Ceiling Function - Ceiling returns the smallest integer greater than or equal to the given numeric expression.

Floor Function - Floor returns the largest integer less than or equal to the given numeric expression.

Math Functions - Various Mathematical Functions

Pi Function - Returns the mathematical constant Pi.

Number (16) - Procedures useful for numbers.

AddLeadingZeros - Creates a number having a given length, by adding zeros to the front of the number until the length is met.

BaseN2Decimal Function - Converts any base to base 10.

CBin Function - Convert a numeric value to binary

Decimal2BaseN Function - Converts base 10 to any base.

Hex2Dec Function - Converts an Hexadecimal value to Decimal.

IncrTextString Function - Increment a Text String (from ABY to ABZ to AZA etc.)

Int32Parse Function - Replaces Int32.Parse() method from ASP.NET

IsNaN Function - Determines whether or not a variant is numeric and convertable to a sub type of double.

IsOdd Function - Determines if a number is odd as opposed to even.

IsPrime Function - Checks to see if a number is a primary number.

IsWhole Function - IsWhole determines whether or not a numeric variant is a whole number or not.

MkRnd Function - Creates a random number of a specified length.

NewCInt Function - A more convenient CInt.

NumberToLetter Function - Convert any number to a string equivalent (like Excel column letters)

NumberToString Function - Convert any number to a string

ProperNum Function - Returns a properly formatted number: 1 > 1st, 123 > 123rd.

Other (9) - Miscellaneous procedures

CheckReferer Function - Validate referer URLs.

ExpireImage Function - Show a expiry image

IIF Function - IIF Function from Visual Basic.

ListApplications Statement - List all Application variables

ListSessions Statement - List all Session variables.

NATOSpelling Procedure - Convert to the NATO international phonetic alphabet

QueryStringBuilder Function - This function replaces or add specified variables In the existing QUERY_STRING

RequestToVar Function - Retrieve all variables from each type of Request and assign variables to them

W Statement - Response.Write replacer

Path (6) - Procedures for working with absolute and virtual paths.

GetAbsolutePath Function - Convert a relative path to an absolute path

GetCurPageName Function - This function let you get the current page name.

GetCurPagePath Function - This function let you get the current page path.

GetCurPageURL Function - How to get the current page URL

Root Function - Returns the root folder of the server.

UnMappath Function - Returns a string representing the virtual location of an absolute path.

String (38) - Functions to manipulate, alter and check strings.

AddChr Function - Adds characters to a string in a specific location.

AddZero Function - Adds leading zeroes to a specified length.

BinaryToString Function - Converts the binary content to text.

CountWord Function - Counts the number of words in a string.

Format Function - Formats a number, date or time based on the user entered format type.

FormatString Function - Formats a string upon a reference mask.

FormatTxt Function - Formats a string with special HTML characters.

GetAlphaRegExp Function - Get only non numerics from a given alphanumeric string.

GetNumberRegExp Function - Get only numbers from a given alphanumeric string.

HexToStr Function - Converts an Hexadecimal string to its value.

InstrCount Function - Count the number of occurrences of a character sequence in a String

IsAlpha Function - Allows only white space, letters of the alphabet, and underscore characters to pass as valid input.

IsAlphaNumeric Function - Allows only white space, letters, numbers, and underscore characters to pass as valid input.

IsBlank Function - Check if a variable, string, object or array is empty

IsLike Function - Compares a string to a regular expression.

LinkURLs Function - Transforms URL or Email to Links

MidRev Function - Same as Mid() Function but in reverse.

OnlyNumbers Function - Trim Non-numeric Characters Using ASCII

RandomPassword Function - Generate a random password of defined length.

RemChr Function - Removes unwanted characters from a string.

RepCR Function - Replace carriage returns with HTML BR

StrBanned Function - Ban form input containing unwanted text

StrCensor Function - Removes disallowed words from a string.

StrCheck Function - Checks a string for a specified length.

StrCutOff Function - This function allows to control the length of a string.

StrExpand Function - Expands a string by inserting spaces between characters and three spaces when it encounters a space.

StripASPCodeRegExp Function - Strips/replaces any ASP Code that may be found in a string.

StripHTML Function - Removes HTML code from a string.

StripHTMLRegExp Function - Allow users to post safe HTML to your site.

StripSpaces Function - Removes all white space from a string.

StrRepeat Function - VBScript String() Function replacement that supports multiple characters

StrToHex Function - Converts a string to its hex value.

TrimCHR Function - Trim leading and trailing comma or other character or string from a string.

TrimEx - Trims a string of all leading and trailing whitespace.

UniqueChars Function - Return and Count Unique Characters from a String

URLDecode Function - Decodes a urlencoded string.

URLDecode2 Function - Decodes a urlencoded string.

ValidationCode Function - Generate a random code of defined length.

««

Page rendered in: 375 ms

Privacy

Cookies Policy

License

Terms of Use

Report Errors

Link to Livio.net