Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

SQL DateTime Function

Posted by Praveen Kumar on May 3, 2008

Sql Datetime function-get month,day,year ANOTHER WAY

CREATE FUNCTION dbo.GetDateOnly ( @InputDate DATETIME )
RETURNS DATETIME
BEGIN

RETURN CAST(CONVERT(VARCHAR(10), @InputDate, 111) AS DATETIME)

END
GO

Sql Datetime function-get month,day,year

CREATE FUNCTION dbo.GetDateOnly ( @pInputDate DATETIME )

RETURNS DATETIME

BEGIN

RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + ‘/’ +

CAST(MONTH(@pInputDate) AS VARCHAR(2)) + ‘/’ +

CAST(DAY(@pInputDate) AS VARCHAR(2)) AS DATETIME)

END

GO

Format DateTime C#

string formattedDate;

// Format : 07 / 03 / 2004

formattedDate = DateTime.Now.ToString(“dd / MM / yyyy”);

// Format : 7 / 3 / 2004 (without the preceding zeroes)

formattedDate = DateTime.Now.ToString(“d / M / yyyy”);

// Format : 07 / Mar / 2004

formattedDate = DateTime.Now.ToString(“dd / MMM / yy”);

// How to get the name of the day ?

// Format : Wednesday

formattedDate = DateTime.Today.DayOfWeek.ToString();

Details:
d The day of the month. Single-digit days will not have a leading
zero.

dd The day of the month. Single-digit days will have a leading zero.

ddd The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.

dddd The full name of the day of the week, as defined in DayNames.

M The numeric month. Single-digit months will not have a leading zero.

MM The numeric month. Single-digit months will have a leading zero.

MMM The abbreviated name of the month, as defined in AbbreviatedMonthNames.

MMMM The full name of the month, as defined in MonthNames.

y The year without the century. If the year without the century is less than 10,
the year is displayed with no leading zero.

yy The year without the century. If the year without the century is less than 10,
the year is displayed with a leading zero.

yyyy The year in four digits, including the century.

gg The period or era. This pattern is ignored if the date to be formatted does
not have an associated period or era string.

h The hour in a 12-hour clock. Single-digit hours will not have a leading zero.

hh The hour in a 12-hour clock. Single-digit hours will have a leading zero.

H The hour in a 24-hour clock. Single-digit hours will not have a leading zero.

HH The hour in a 24-hour clock. Single-digit hours will have a leading zero.

m The minute. Single-digit minutes will not have a leading zero.

mm The minute. Single-digit minutes will have a leading zero.

s The second. Single-digit seconds will not have a leading zero.

ss The second. Single-digit seconds will have a leading zero.

f The fraction of a second in single-digit precision. The remaining digits are truncated.

ff The fraction of a second in double-digit precision. The remaining digits are truncated.

fff The fraction of a second in three-digit precision. The remaining digits are truncated.

ffff The fraction of a second in four-digit precision. The remaining digits are truncated.

fffff The fraction of a second in five-digit precision. The remaining digits are truncated.

ffffff The fraction of a second in six-digit precision. The remaining digits are truncated.

fffffff The fraction of a second in seven-digit precision. The remaining digits are truncated.

t The first character in the AM/PM designator defined in AMDesignator or PMDesignator, if any.

tt The AM/PM designator defined in AMDesignator or PMDesignator, if any.

z The time zone offset (”+” or “-” followed by the hour only). Single-digit hours will
not have a leading zero. For example, Pacific Standard Time is “-8″.

zz The time zone offset (”+” or “-” followed by the hour only). Single-digit hours
will have a leading zero. For example, Pacific Standard Time is
“-08″.

zzz The full time zone offset (”+” or “-” followed by the hour and minutes). Single-digit
hours and minutes will have leading zeros. For example, Pacific Standard Time is “-08:00″.

: The default time separator defined in TimeSeparator.

/ The default date separator defined in DateSeparator.

% c – Where c is a format pattern if used alone. The “%” character can be omitted if the
format pattern is combined with literal characters or other format patterns.

\ c – Where c is any character. Displays the character literally. To display the backslash
character, use “\\”.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: