SQL: Date functions, format, conversion

Get the start of the month:

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

  1. It finds the difference of the date between the SQL calendar start date (1900-01-01) in month and
  2. then add that difference in the month to the SQL calendar  start date
  3. Another way:
    SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))

End of the month:

SELECT EOMONTH(@mydate) AS EndOfMonth

Validate a column if it is DATE

select ISDATE(dob) from eployee;

Find the name of the Day, Wednesday:

select datename(weekday, convert(datetime, @today)) as datename;

Regular date expression are entered in year, month, day format:

yyyymmdd: ‘20051128’ 2005 v 28th

Convert the date to datename, monthname (01 January 2016) format: 

The output format has to be varchar data type not as date datatype. Input when you convert to datetime; output when you convert to character data. Input will show how it will store the data in the SQL server, output will display the result to the user screen according to style format:

select convert(nvarchar, GETDATE(), 106) AS spdate;

select convert(varchar, convert(date, ’12/04/2016′, 103), 106) as spdate;
select convert(date, convert(date, ’12/04/2016′, 103), 106) as spdate;

select convert(varchar, cast(’12/04/2016′ as date), 106) as spdate;
select convert(date, cast(’12/04/2016′ as date), 6) as spdate;

Check if the date is the First Friday of the Month:

declare @today datetime2;
declare @isFirstFriday bit;
set @today=CONVERT(datetime2, ’06/05/2016′);
set @isFirstFriday=0;
set @isFirstFriday= ( CASE WHEN ((DATEPART(DAY, @today)<=7 and datename(weekday, @today)=’Friday’)) THEN 1
ELSE 0
END);
select @isFirstFriday as IsFirstFriday;
select datename(weekday, @today) as datename;
–Check if the date is First Friday of the Month
declare @isFirstFriday bit=0;
set @isFirstFriday= ( CASE WHEN ((DATEPART(DAY, GETDATE())<=7 and datename(weekday, GETDATE())=’Friday’)) THEN 1
ELSE 0
END);
select @isFirstFriday as IsFirstFriday;
select datename(weekday, GETDATE()) as datename;

Copy the time portion of a DATETIME2

Using SSIS package, when the datetime datatype is copied from from one sql table to another table, the year month and day are copied but the time component is set to 00:00:00.000. To copy the time portion as well use the data type datetimeoffset(n)

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';
DECLARE @datetime datetime = @datetimeoffset;

SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';

SQL:
Default value of datetime2 column getdate() was setting the date but not the time component. It was setting time to 00:00:00.000

In that table in SQL Server, specify the default value of that column to be CURRENT_TIMESTAMP.

DataType of that column may be DateTime or datetime2.

C# Date format:

DateTime.Now.ToString(“yyyyMMdd_HHmmss”);

Find the name of the month or name of the day

DATENAME(weekday, GETDATE())

It returns Friday

Get the Week start date and week last  date:

@@DATEFIRST (Transact-SQL) SET DATEFIRST specifies the first day of the week. The U.S. English default is 7, Sunday.

To change the week begin day either change the language settings or

SET DATEFIRST 3.

DECLARE @today tinyint;
SELECT @today=DATEPART(WEEKDAY, GETDATE());
SELECT
GETDATE() AS TodayDate
,@@DATEFIRST AS FirstDayOfTheWeek
,DATEADD(DAY, – @today, GETDATE()) AS WeekStartDate
,DATENAME(WEEKDAY, DATEADD(DAY, – @today + 1, GETDATE())) AS WeekStartDay
/*
Say in your language settings Sunday is the week start day
Today is Thursday when you are running your query.
So from Sunday – Thursday the number of days is 5 including Sunday.
If we deduct 5 days from today it will return Saturday.
That is why, in order to get the correct Start date we have add deduct 1 day less
*/
,DATENAME(WEEKDAY, DATEADD(DAY, @@DATEFIRST- @today, GETDATE())) AS WeekEndDay
,@today AS ‘TodayNumberInThisWeek’

C# Format DateTime AS yyyyMMddHHmmss

DateTime.Now.ToString("yyyyMMddHHmmss"); // case sensitive

T-SQL Format DateTime AS yyyyMMddHHmmss

SELECT format(getdate(),’yyyyMMdd_HHmmss’)

SELECT format(getdate(),’D’, ‘en-gb’)
SELECT format(getdate(),’D’, ‘en-US’)

SSIS Format DateTime AS yyyyMMddHHmmss

Need to declare the variable as string. Get the current date by getdate() then convert the getdate() datepart value to string.

(DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

Check if Date only then Convert otherwise use default Date–>convert to nvarchar, 112 format–>convert to INT

CONVERT(INT, CONVERT(nvarchar(10), (CASE WHEN ISDATE(DateOfBirth)=1 THEN CONVERT(DATE, DateOfBirth)

ELSE CONVERT(DATE, ‘1899-12-30’) END), 112)) AS DateOfBirth

input DOB DATE format is: nvarchar(255) – 31/07/1940

desired output: 19400731

CONVERT(NVARCHAR(10), CONVERT(DATE, DOB, 103), 112)

 

 

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