SQL Server Date, Time and DateTime Functions

 

 

user defined functions

 

create  function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end
go

create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
    begin
    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
    end
go

 
create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
    begin
    return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
    end
go

create function TimeOnly(@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
-- Thanks, Peso!
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
    begin
    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
    end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Examples:

• where TransactionDate >= dbo.Date(2005,1,2)  -- no formatting or implicit string conversions needed for date literals
• select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.
• select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.
• if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0)  -- check to see if the time for a given date is at 5:30 AM
• select dbo.Date(year(getdate()), month(getdate()),1) -- returns the first day of the current month.
• select dbo.Date(year(getdate()), month(getdate())+1,0) -- returns the last day of the current month.
• where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 -- a simple way to get all transactions that occurred on the current date
• select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) -- returns tomorrow at 2:30 PM.

 

Ref : http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Resetting Identity Column SQL Server

 

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever the value you want. As an example is if you wanted to start numbering my ID column at 1000 instead of 100.

First check the current identify value using:

DBCC CHECKIDENT (’tablename’, NORESEED)

To set the value of the next ID to be 1000, use this command:

DBCC CHECKIDENT (’tablename’, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Note: Enclose the table name in single quotes or square brackets if you are referring by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ( ‘databasename.dbo.tablename’,RESEED, 999)

 

My Problem:

By Mistake I entered one record in production server with ID (Identity Column) 1051.

SO 1st I deleted the record with ID = 1051 and then executed the following command immediately:

DBCC CHECKIDENT ('TableName', RESEED, 1050)

So Now There wont be any gaps while inserting from now onwards.

Copy Website to remote server through FTP in Visual Studio

 

 

To start, you will connect to the FTP Web site from within Visual Web Developer. You must know the FTP address of the FTP Web site and, if it is required, you must have a user name and password.

To access the FTP Web site

1. In Visual Web Developer, on the File menu, click New Web Site.

2. In the New Web Site dialog box, in the left-most Location list, click FTP Site, and then click Browse.

3. In the Choose Location dialog box, in the Server and Directory boxes, enter the appropriate information.

   The Server field refers to the name or IP address of the FTP server. The Directory field refers to a specific directory on the FTP server that you want to put the content in. By default, many FTP sites connect you to a home directory, although there might still be a directory below that or a separate virtual directory where the Web content should be put. If you do not know these values, contact the FTP site administrator.

   For Port, the most common value is 21, but you should confirm that value with the FTP site administrator also.

4. Examine the following values:

   • Passive Mode

     Leave this check box clear at first. Passive mode is sometimes required, if there is a firewall between your server and the FTP server.

   • Anonymous Login

     If the FTP site administrator has provided you with a user name and password, clear the Anonymous Login check box, and then in the Username andPassword boxes, enter the appropriate information.

5. Click Open.

   Visual Web Developer will try to connect to the FTP server by using the information that you have provided. If the connection is successful, Visual Web Developer displays the FTP Web site name in Solution Explorer and lists the files that are already in the FTP Web site.

Troubleshooting

If the connection fails, examine the following:

• If you have administrative rights on the server, use IIS Manager to add the Write permission to the FTP virtual directory. Do not complete this before you have set restricted NTFS File System permissions on the virtual directory, either by using IIS Manager or Microsoft Windows Explorer. For more information, go to Microsoft Technet and search for information about how to help secure FTP sites.

• Confirm that the server name and directory name are correct.

• Use the URL of the Web site or the FTP site in the Server field and leave the Directory field blank.

• Try connecting with Passive Mode enabled. This frequently lets you get through a firewall.

• Make sure that the FTP Web site has the directory that you have indicated. If you are using IIS as the FTP server, remember that the directory is typically defined under the ftproot directory, not the wwwroot directory.

• Contact the FTP site administrator to determine whether anonymous log on is allowed. If not, make sure that you have credentials for a user account that has permission to access and modify the FTP directory.