SYBASE: DATEPART function

datepart

Description

Returns the specified datepart in the first argument of the specified date (the second argument) as an integer. Takes a date, time, datetime, or smalldatetime value as its second argument. If the datepart is hour, minute, second, or millisecond, the result is 0.

Syntax

datepart(date_part, date expression)

Parameters

date_part

is a date part. Table 2-7 lists the date parts, the abbreviations recognized by datepart, and the acceptable values.

Table 2-7: Date parts and their values
Date part	Abbreviation	Values
year	yy	1753 – 9999 (2079 for smalldatetime). 0001 to 9999 for date
quarter	qq	1 – 4
month	mm	1 – 12
week	wk	1 – 54
day	dd	1 – 31
dayofyear	dy	1 – 366
weekday	dw	1 – 7 (Sun. – Sat.)
hour	hh	0 – 23
minute	mi	0 – 59
second	ss	0 – 59
millisecond	ms	0 – 999
calweekofyear	cwk	1 – 53
calyearofweek	cyr	1753 – 9999 (2079 for smalldatetime). 0001 to 9999 for date
caldayofweek	cdw	1 – 7

When you enter a year as two digits (yy):

·  Numbers less than 50 are interpreted as 20yy. For example, 01 is 2001, 32 is 2032, and 49 is 2049.

·  Numbers equal to or greater than 50 are interpreted as 19yy. For example, 50 is 1950, 74 is 1974, and 99 is 1999.

Milliseconds can be preceded by either a colon or a period. If preceded by a colon, the number means thousandths of a second. If preceded by a period, a single digit means tenths of a second, two digits mean hundredths of a second, and three digits mean thousandths of a second. For example, “12:30:20:1” means twenty and one-thousandth of a second past 12:30; “12:30:20.1” means twenty and one-tenth of a second past 12:30.

date expression

is an expression of type datetime, smalldatetime, date, time, or a character string in a datetime format.

Examples

Example 1

Assumes a current date of November 25, 1995:

select datepart(month, getdate())

-----------

          11

Example 2

Returns the year of publication from traditional cookbooks:

select datepart(year, pubdate) from titles where type = "trad_cook"

 -----------

        1990 

        1985 

        1987 

Example 3

select datepart(cwk,’1993/01/01’)

-----------

          53

Example 4

select datepart(cyr,’1993/01/01’)

-----------

        1992

Example 5

select datepart(cdw,’1993/01/01’)

-----------

           5

Example 6

Find the hours in a time:

declare @a time

select @a = "20:43:22"

select datepart(hh, @a)

-----------

    20

Example 7

If a hour, minute, or second portion is requested from a date using datename or datepar) the result is the default time, zero. If a month, day, or year is requested from a time using datename or datepart, the result is the default date, Jan 1 1900:

--Find the hours in a date

declare @a date

select @a = "apr 12, 0001"

select datepart(hh, @a)

-----------

    0

--Find the month of a time

declare @a time

select @a = "20:43:22"

select datename(mm, @a)

------------------------------

January

When you give a null value to a datetime function as a parameter, NULL is returned.

Usage

·         datepart, a date function, returns an integer value for the specified part of a datetime value. For more information about date functions, see “Date functions”.

·         datepart returns a number that follows ISO standard 8601, which defines the first day of the week and the first week of the year. Depending on whether the datepart function includes a value for calweekofyear, calyearofweek, or caldayorweek, the date returned may be different for the same unit of time. For example, if Adaptive Server is configured to use U.S. English as the default language, the following returns 1988:

·         datepart(cyr, "1/1/1989")

However, the following returns 1989:

datepart(yy, "1/1/1989)

This disparity occurs because the ISO standard defines the first week of the year as the first week that includes a Thursday and begins with Monday.

For servers using U.S. English as their default language, the first day of the week is Sunday, and the first week of the year is the week that contains January 4th.

·         The date part weekday or dw returns the corresponding number when used with datepart. The numbers that correspond to the names of weekdays depend on the datefirst setting. Some language defaults (including us_english) produce Sunday=1, Monday=2, and so on; others produce Monday=1, Tuesday=2, and so on.You can change the default behavior on a per-session basis with set datefirst. See the datefirst option of the set command for more information.

·         calweekofyear, which can be abbreviated as cwk, returns the ordinal position of the week within the year. calyearofweek, which can be abbreviated as cyr, returns the year in which the week begins. caldayofweek, which can abbreviated as cdw, returns the ordinal position of the day within the week. You cannot use calweekofyear, calyearofweek, and caldayofweek as date parts for dateadd, datediff, and datename.

·         Since datetime and time are only accurate to 1/300th of a second, when these datatypes are used with datepart, milliseconds are rounded to the nearest 1/300th second.

·         Since smalldatetime is accurate only to the minute, when a smalldatetime value is used with datepart, seconds and milliseconds are always 0.

·         The values of the weekday date part are affected by the language setting

ENGLISH DICTIONARY: DWARF

1.   dwarf

dwôrf/

noun

noun: dwarf; plural noun: dwarfs; plural noun: dwarves; noun: dwarf star; plural noun: dwarf stars

1.   1.

(in folklore or fantasy literature) a member of a mythical race of short, stocky humanlike creatures who are generally skilled in mining and metalworking.

synonyms:

gnome, goblin, hobgoblin, troll, imp, halfling, elf, leprechaun More "the wizard captured the dwarf"

§  offensive

an abnormally small person.

synonyms:

small person, short person; More manikin, homunculus

§  denoting something, esp. an animal or plant, that is much smaller than the usual size for its type or species.

modifier noun: dwarf

"a dwarf conifer"

synonyms:		miniature, small, little, tiny, pocket, diminutive, baby, stunted, undersized, undersize; More informalmini, teeny, teeny-weeny, itsy-bitsy, pint-sized, little-bitty, vertically challenged; wee "dwarf conifers"
antonyms:	giant

2.   2.

Astronomy

a star of relatively small size and low luminosity, including the majority of main sequence stars.

verb

verb: dwarf; 3rd person present: dwarfs; past tense: dwarfed; past participle: dwarfed; gerund or present participle: dwarfing

3.   1.

cause to seem small or insignificant in comparison.

"the buildings surround and dwarf All Saints Church"

EX: Snow White and the Seven Dwarfs is a 1937 American animated film. In this movie princess was living with seven adult dwarfs, Doc, Grumpy, Happy, Sleepy, Bashful, Sneezy, and Dopey, who work in a nearby mine.

EXCEL:YEARFRAC Example

If we remember the the date of joining tpo the field, then we can calculate the exact experience to store in excel.

YEARFRAC(start_date, End_date, [Basis])

Here Start_date is Date of Joining.

End_Date should be todays date for which we can use the function “TODAY()”.

[Basis] is not required to fill up.

Ex: If my Date of Joining is December 18, 2008

In Excel I will write as below:

=YEARFRAC(DATE(2008,12,18),TODAY())

If today is August 20, 2013, My experience will be shown as 4.672222.

 

GREENPLUM: CREATE TABLE SYNTAX EXAMPLES

Create a table named rank in the schema named baby and distribute the data using the columns rank, gender, and year:

CREATE TABLE baby.rank (id int, rank int, year smallint, gender char(1), count int ) DISTRIBUTED BY (rank, gender, year);

Create table films and table distributors (the primary key will be used as the Greenplum distribution key by default):

CREATE TABLE films (code char(5) CONSTRAINT firstkey PRIMARY KEY,title varchar(40) NOT NULL,did integer NOT NULL,date_prod date,kind varchar(10),len interval hour to minute);

CREATE TABLE distributors (did integer PRIMARY KEY DEFAULT nextval('serial'),name varchar(40) NOT NULL CHECK (name <> ''));

Create a gzip-compressed, append-only table:

CREATE TABLE sales (txn_id int, qty int, date date) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (txn_id);

Create a three level partitioned table using subpartition templates and default partitions at each level:

CREATE TABLE sales (id int, year int, month int, day int, region text)DISTRIBUTED BY (id)PARTITION BY RANGE (year) SUBPARTITION BY RANGE (month) SUBPARTITION TEMPLATE ( START (1) END (13) EVERY (1), DEFAULT SUBPARTITION other_months ) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION asia VALUES ('asia'), DEFAULT SUBPARTITION other_regions)( START (2002) END (2010) EVERY (1), DEFAULT PARTITION outlying_years);

EXCEL: know about YEARFRAC()

It calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

Syntax

YearFrac(Arg1, Arg2, Arg3)

Parameters

Name	Required/Optional	Data Type	Description
Arg1	Required	Variant	Start_date - a date that represents the start date.
Arg2	Required	Variant	End_date - a date that represents the end date.
Arg3	Optional	Variant	Basis - the type of day count basis to use.

 Important   Dates should be considered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Basis	Day count basis
0 or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
• All arguments are truncated to integers.
• If start_date or end_date are not valid dates, YEARFRAC returns the #VALUE! error value.
• If basis < 0 or if basis > 4, YEARFRAC returns the #NUM! error value.

SYBASE: sp_addlogin to create a login in a specific server and database.

sp_addlogin

Description

Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.

Syntax

sp_addlogin loginame, passwd [, defdb] 

        [, deflanguage] [, fullname] [, passwdexp]

        [, minpwdlen] [, maxfailedlogins] [, auth_mech]

Parameters

loginame

is the user’s login name. Login names must conform to the rules for identifiers.

passwd

is the user’s password. Passwords must be at least 6 characters long. If you specify a shorter password, sp_addlogin returns an error message and exits. Enclose passwords that include characters besides A – Z, a – z, or 0 – 9 in quotation marks. Also enclose passwords that begin with 0-9 in quotation marks.

defdb

is the name of the default database assigned when a user logs into Adaptive Server. If you do not specify defdb, the default, master, is used.

deflanguage

is the official name of the default language assigned when a user logs into Adaptive Server. The Adaptive Server default language, defined by the default language id configuration parameter, is used if you do not specify deflanguage.

fullname

is the full name of the user who owns the login account. This can be used for documentation and identification purposes.

passwdexp

specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.

minpwdlen

specifies the minimum password length required for that login. The values range between 0 and 30 characters.

maxfailedlogins

is the number of allowable failed login attempts. It can be any whole number between 0 and 32767.

auth_mech

defines the authentication mechanism.

Examples

Example 1

Creates an Adaptive Server login for “albert” with the password “longer1” and the default database corporate:

sp_addlogin albert, longer1, corporate

Example 2

Creates an Adaptive Server login for “claire.” Her password is “bleurouge,” her default database is public_db, and her default language is French:

sp_addlogin claire, bleurouge, public_db, french

Example 3

Creates an Adaptive Server login for “robertw.” His password is “terrible2.” his default database is public_db, and his full name is “Robert Willis.” Do not enclose null in quotes:

sp_addlogin robertw, terrible2, public_db, null, "Robert Willis"

Example 4

Creates a login for “susan” with a password of “wonderful,” a full name of “Susan B. Anthony,” and the server’s default database and language. Do not enclose null in quotes:

sp_addlogin susan, wonderful, null, null, "Susan B. Anthony"

Alternately, you can also use the following:

sp_addlogin susan, wonderful, @fullname="Susan B. Anthony"

Example 5

Configures the login “mylogin” to override global authentication mechanisms:

sp_addlogin mylogin, mypassword, @auth_mech = ASE

Usage

·         For ease of management, it is strongly recommended that all users’ Adaptive Server login names be the same as their operating system login names. This makes it easier to correlate audit data between the operating system and Adaptive Server. Otherwise, keep a record of the correspondence between operating system and server login names.

·         After assigning a default database to a user with sp_addlogin, the Database Owner or System Administrator must provide access to the database by executing sp_adduser or sp_addalias.

·         auth_mech can take the same values as sp_modify login "authenticate with" option.

·         Although a user can use sp_modifylogin to change his or her own default database at any time, a database cannot be used without permission from the Database Owner.

·         A user can use sp_password at any time to change his or her own password. A System Security Officer can use sp_password to change any user’s password.

·         A user can use sp_modifylogin to change his or her own default language. A System Administrator can use sp_modifylogin to change any user’s default language.

·         A user can use sp_modifylogin to change his or her own fullname. A System Administrator can use sp_modifylogin to change any user’s fullname.

Permissions

Only a System Security Officer can execute sp_addlogin.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	·         Roles – Current active roles ·         Keywords or options – NULL ·         Previous value – NULL ·         Current value – NULL ·         Other information – All input parameters ·         Proxy information – Original login name, if set proxy in effect