SQL DateTime Archives - Page 16 of 16 - SQL Authority with Pinal Dave

SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar

June 8, 2007

In analytics section of our product I frequently have to display the current week dates with days. Week starts from Sunday. We display the data considering days as column and date and other values in column. If today is Friday June 8, 2007. We need script which can provides days…

SQL SERVER – Script/Function to Find Last Day of Month

May 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Utility

Following query will find the last day of the month. Query also take care of Leap Year. Script: DECLARE @date DATETIME SET @date='2008-02-03' SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS LastDayOfMonth GO DECLARE @date DATETIME SET @date='2007-02-03' SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS LastDayOfMonth GO ResultSet: LastDayOfMonth ----------------------- 2008-02-29 00:00:00.000 (1 row(s) affected)…

SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

May 13, 2007

Pinal Dave

SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Server, SQL Utility

Following query will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.

SQL SERVER – UDF – User Defined Function to Find Weekdays Between Two Dates

May 5, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Utility

Following user defined function returns number of weekdays between two dates specified. This function excludes the dates which are passed as input params. It excludes Saturday and Sunday as they are weekends. I always had this function with for reference but after some research I found original source website of…

SQL SERVER – @@DATEFIRST and SET DATEFIRST Relations and Usage

April 22, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Utility

The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as first day of the week using…

SQL SERVER – SQL Commandments – Suggestions, Tips, Tricks

March 20, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, Database, Software Development, SQL Backup and Restore, SQL Constraint and Keys, SQL DateTime, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Joins, SQL Server DBCC, SQL Server Security, SQL Stored Procedure

Few days ago, while searching for something on web site, I came across a very good article of 25 SQL Commandments. I really enjoyed reading it. It was for Oracle, I re-wrote it for SQL Server. First 18 points are taken from original article and last 2 I added to…

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

November 30, 2006

Pinal Dave

SQL Tips and Tricks

SQL Cursor, SQL DateTime, SQL Scripts, SQL Server, SQL Stored Procedure

Following cursor query runs through the database and find all the table with certain prefixed (‘b_’,’delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other operation on that table like to delete, print or index.