Working with Datetime

Leo Peysakhovich from SQLServerCentral.com

Datetime data in SQL Server can be a little confusing to work with, especially as many front end languages do not combine the date and time into a single datatype. As a result, T-SQL is sometimes used to manipulate and convert datetime data into more useable formats. Author Leo Peysakhovich has written about some tricks that he uses to reformat and work with datetime data. More »

Strategies for Testing Data Warehouse Applications

Additional Articles from Datawarehouse.com

This article presents a strategy for testing ETL applications that perform data movement and populate data warehouses. It is useful information for the testing community as well as designers and managers charged with planning a data warehousing implementation.More »

Changing Service Credentials

Additional Articles from SimpleTalk

One day, you may need to change those service credentials under which your SQL Server services normally run. If you have a number of servers, then you'll really want to read about Rodney's solution. More »

One Year Ago - Database Snapshots

Kumar Part from SQLServerCentral.com ( published: 09/21/2006)

Since I've spent most of the week at the PASS Summit, and have been busy meeting with people there instead of working on this site, I opted for a more fun, less thought-requiring poll.

Also so many of those people that comment regularly are also at the conference, so I'd hate to write an editorial and get no responses :(

So this week we step away from technology, and the serious side of things. We work hard and most of us are busy solving problems in technology all the time, so here's an easy one.

What are you doing to mark a special event?

I'll apologize in advance that I didn't have any great answer to this question.

I turned 40 recently and it was a fairly calm, quiet day. I handled some regular business with the kids, including a Cub Scout outing, did a little work (hey, the grind never stops here), and had a nice dinner with the family. I'm at peace with my aging, so this wasn't the big deal it's been for a few friends.

So it could be a birthday, anniversary, or other memorable event in your life. I'm just curious what types of things you might do for yourself or someone else in your life.

No need to mention which event or who it's for, but you might spark an idea in someone else's head for their next event.

Steve Jones

» Join the debate, and respond to today's editorial on the forums

» To submit a tip, rant or editorial, email articles@sqlservercentral.com.

Today's Question:

If a SQL Server 2005 Integration Services package runs successfully, what happens to the checkpoint file?

Think you know the answer? Click Here, and find out if you are right.

This Question is worth 1 Point(s) in this category : SQL Server 2005 - SSIS. We keep track of your score to give you bragging rights against your peers. If you have problems with submitting you can go to the QOD section inside the Test Center directly at http://www.sqlservercentral.com/testcenter/QOD.asp. We'd love to give you credit for your own question and answer. To submit a QOD, simply send the question, answers and explanation to articles@sqlservercentral.com

Today's Question is sponsored by:
 

You have created all necessary Service Broker objects. Now you want to send a message from one Service Broker service to another Service Broker service. What?s the name of the correct T-SQL statement? Sponsored by Apress and to Klaus Aschenbrenner, author of Pro SQL Server 2005 Service Broker

Answer: BEGIN DIALOG CONVERSATION

Explanation: The correct answer is the BEGIN DIALOG CONVERSATION T-SQL statement is used to create a new conversation between two Service Broker services.

Ref: Pro SQL Server 2005 Service Broker
BEGIN DIALOG CONVERSATION - http://msdn2.microsoft.com/en-us/library/ms187377.aspx

Here's a few of the new posts today on the forums. To see more, select Discussion Forums in the Community menu on the site:

   index comparision between two database - Hi All,           This weekend i backup and restored production database in my UAT server. After the refresh i did some column value changes in one table also i did the s....(more online)

   use Native XML web service in sql 2005 -  envirvorment:  XP sp2, IIS 5, sql server 2005 dev edition. 1, created a end point  CREATE ENDPOINT sql_endpoint STATE = STARTEDAS HTTP(PATH = '/sql_endpoint', AUTHENTICATION = (INTEGRATED ), PORTS = ( CLE....(more online)

   db_datawriter in SQL3005 - Hello,My situation is a read-only database and a work aread where users can pull subsets from the ro-DB. I am trying to understand the Fixed database role db_datawriter. When I grant db_datawriter to a user, I expect him/her ....(more online)

   error 17806, 18542 -- login - I have run across these error message in many postings, but I can't find any solutions that fit my situation.  The server is question is runningwin 2003 and SQL Server 2005.  It was recently refreshed and was taken ....(more online)

   writting a script to add PKs constraints on columns in many tables at once - Hello there,I need help fast. I'm new to SQL server. I'm on a new project where i'm being asked to create a script to add PK constraints in many table at once. I found online a script to add PKs but how to do it all at once i....(more online)

   Problem with Maintenance script - Hello,I am new to the site would like to say hey and thank everyone for the useful info I have gotten so far!   That being said I have a problem that maybe some of the gurus maybe able to help with. I have a SQ....(more online)

   Msg 3271 - I received the following error message when attempting to backup a database. Msg 3271, Sev 16: Nonrecoverable I/O error occurred on file I looked on Microsofts web site and found nothing that helps.ThanksGlen....(more online)

   Who dropped my table ? - Hi,I want to find out how a particular table in a DB got dropped....all the info regarding that...SOSAlso is there a way that I can restore the dropped table in SS2K5, as we've in Oracle 10G, the Flashback command?Thanks,Saya....(more online)

   Cube Responsibilities: When to use then and when not - Hi Guys, I have a very simple question that needs to be answered and as said in the heading, what are the responsibilities of a cube? Let me give you some scenarios: Scenario 1:A client purchases a new product, the produ....(more online)

   SSReportingServices 2005 - Data Manipulation - Hi All,I developing a SSRS 2005 Report and I would like to update data back to table via the Report. If the column has a name of TestName, I would like to update "TestName" to John Doe.I haven't found a lot of documentation o....(more online)

   Limiting access to cube via Pivot Table - Hello, I would like to limit access to a particular cube by forcing the user to enter a particular account number, instead of allowing the default of all accounts.  i believe that the InstanceSelection element is the key....(more online)

   Help with an error - being fairly new to SQL2005 and Buisness Intelligence, i thought if make a simple project using the data wizard to copy data from an old SQL2000 Server to the SQL2005 server.  I managed this quite easily, and also manage....(more online)

   Conversion failed when converting the varchar value xx to int - Problem hope someone can help me.  I am getting this error message when trying to update a row through an asp page:I am using visual studio 2005 and sql2005Conversion failed when converting the varchar value '@JobLevel' ....(more online)

   Academic Developer Edition Install from Visual Studio Pro Package - Hello,I have purchased an original Academic version of MS Visual Studio 2005. Included in the 11 disks are two SQL Server 2005 Developer Edition 32-bit disks. I have an existing SQL Server 2000 setup on my machine and upon lo....(more online)

   Knowing system table value changes - Dear Friends....                 How can i know that if i change any table structure. is there any other effect is occure in any system table the....(more online)

   sp execution - table definiation;USE [Rainbow]GO/****** Object: Table [UserDataEntry].[GeneralInfo] Script Date: 09/20/2007 09:55:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [UserDataEntry].[Gene....(more online)

   Really interesting issue! - Hi All, When I wanted to see ONLY today?s records on the sql server, it took 5 minutes. When I wanted to see records including today?s records on the sql server, no problem. (19 Sep. is a past date which is working ....(more online)

   Execute a stored proc which calls SSIS packages and uses Xp_cmdshell, using a proxy account in sql server 2005 - Hi all,I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this s....(more online)

   SQLDUMPER error when trying to change packageID using dtutil for pkg stored in msdb - Hi All,I?m having a problem changing the ID of an SSIS package stored to the msdb.  I have two subfolders, each underneath the MSDB folder, named MsdbTest and MsdbProd.  The programmer develops the package on the cl....(more online)

   How To Kill UNICODE??? - I can't believe I have to ask this...Start off points:We don't do anything in any other language than English, Never Will!We don't have unlimited storage space to waste so we can prose in any language on earth.Bigger storage ....(more online)

   invoking SSIS package programatically -  Hi,I have a requirement where in i need to keep polling a folder for files and once the file arrives in a folder , i need to invoke ann SSIS package. I don't know how to invoke a package from code. I am thinking, i'll m....(more online)

   Problem: change which worksheet to use from an excel at runtime - I have encountered the following problem: I wish to extract data from several very similar excel files. They each contain data for a single year, and have the same structure and several worksheets. However, the worksheet cont....(more online)

   modification of SSIS package - Hi     Does anyone know if it's possible to modify a SSIS package after it's    created?  Thanks to everyone for being there to help.SQL 9.0.3054....(more online)

   2005 and Memory Management - I've read and noticed SQL 2005 handles memory differently then 2000.  In 2000 if I told a server it had 6GB to use, it allocated the memory.  In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server....(more online)

   Changing the cache ratio on a RAID controller in an HP server - I recently took a look at the configuration of one of our QA servers' RAID controller.  The server is an HP ProLiant DL585 G1.  The RAID controller was configured in a 50/50 config for caching, meaning half of the c....(more online)

   SQL memory usage - My PC is running sql server at 20% cpu and i use a vpn to connect to other pc's in a networkthe request data from excel to my pc, simple stuff really, i updated the pc to a much faster pcthis time i notice that my sqlserver.e....(more online)

   Rebuild Index with SQL Sever Manager - This is not really a problem that I'm having, more a quest for information as to what a rebuild index process is doing in SQL Seerver Manager...We have a large table, 333m rows and occasionaly we need to do bulk insert (c 20m....(more online)

   TempDB CPU - I read an article about having TEMPDB file group for each CPU..We have 4 cpu on some servers but only one TEMPDB Data and Log is it recommended for me to add another TEMPDB ...If any one has done that i like to see how to do ....(more online)

   how do you secure SQL against everyone? - we have a problem. our developers love to write code, not test it and implement it and have applications access sql servers they didn't access previously for new features. problem is that things like blocking comes up along w....(more online)

   DOD Certification - Can anyone tell me where I can find out if SQL Server 2005 is certified for use by the Dept of Defense?....(more online)

   Whether to use replication? - Hi,I currently maintain a fairly busy database running on SQL 2k5 and need to implement an archiving solution.  I would like to replicate the structure of the database each day (a lot of development goes on so there are ....(more online)

   moving db files of a subscriber database - i need to move the primary log files of two databases on a subscriber server to a different disk. i know i can't detach them. i was thinking of stopping MSSQL service and just manually copying the files.i'll get errors when i....(more online)

   Using Variables with openrowset bulk - I'm trying to insert image files into a table in SQL Server 2005. The T-SQL I used works without variables, but when I introduce a variable into the BULK statement, I get errors.The table is  CREATE TABLE [Comp].[Photo](....(more online)

   Question about NULL - Hi,I'd like to query a table excluding all records with a specific value in a column, this column has NULL in it.  so when i run the query select * from tbl1where col1 <> 'value'It displays all records where c....(more online)

   help make query faster please with an index - rsRecordset.Open "select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)",....(more online)

   Inclusive Query Question - I'm not sure exactly how to ask this question, so I will just give sample data and my objectives for the query. I'm trying to create a query that will show actual numbers and target numbers. Here's the issue: some customers....(more online)

   if isdate(5647) returns true, how do you determine if a value is a date - I'm trying to determine if a field has a date on it so I can update the field with a different date format.  The issue I'm having is that, that database field also contains numeric data. One of the fields that is no....(more online)

   Trouble converting varchar to datetime - Hi, I have tried searching the forums for help but none of the solutions i come up with seem to work.  I have a varchar @latestUpdate with the value: 19/09/2007 23:59:59Is there a simple way of converting this to datetim....(more online)

   linked server name - Does sql server 2000 support linked server aliases or names other than the machine name the instance is running on? so instead of 'physical server name.database.owner.object' naming convention in my codee I can use 'physic....(more online)

   Error creating alert from T-SQL - When I run the following query which came right out of BOL, I get this error -- "Server: Msg 14262, Level 16, State 1, Procedure sp_verify_alert, Line 76 The specified @message_id ('55001') does not exist." USE msdb EXEC....(more online)

   sp_execute - When I see SQL Statemnet dbcc inputbuffer of a SPID, I am seeing as (sp_execute; 1). What ever SPID I use, I am seeing the same.How to see the exact SQL statement what they are running? Even with the trace I am not able to se....(more online)

   Cluster failover not happening..plse help - HiI have installed the SQL 2000 cluster  with the SP4 and hotfix for awe and working fine for few hours, before that I have done so many failovers and restart of both the nodes. But the problem happened around 5 PM ....(more online)

   Backup can not be performed on database - Hi there,I have a database on which I perform transactional log backup using maint plan. The TLog backup fails. The error that I can see in the log file is as follows.Microsoft (R) SQLMaint Utility (Unicode), Version Logged o....(more online)

   Log shipping out of sync - Hi Guys,Last night the log shipping went out of sync on one of our databases. The logs that needed to be restored were about 1GB in size from 10:00PM to 15:00AM. I asked the developers whether they did any huge transacti....(more online)

   Moving a SQL 7 database to a 2000 server - Hi all,I have one database on a SQL Server 7 server, which replicates to another version 7 server.  I need to move the database to a SQL Server 2000 server.  I do not want to upgrade the SQL7 servers as there are ot....(more online)

   bcp password prompt - good morning,I have written a sql script to build some bcp out statements in a file to be run as a batch.  In this script, I have coded in the password (I know - not good practice, but its a test system).  Ever....(more online)

   Is CODE a reserved word: SQL 2000? - Or is this problem more related to JDBC?I have a this field name: 'code'. This is the error message i get when i call a stored procedure:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid column name: c....(more online)

   get nextid for non-identity column - hi how do i get the nextid for a non-identity column other then taking the max value and adding 1 to itany help is appreciated....(more online)

   How do I Partition a table by date in SQL server 2000 - I have a table with a couple of million rows in it. Of this I need frequent access to about 70,000 records which are the most recent records.Although there are many date fields in the table I am unable to create a clustered i....(more online)

   Added /3GB And OS Will Not Start - Why? - Here is a new one for me.  I added the /3GB switch to the boot.ini, restarted the server and now receive a number of messages when the OS tries to load.Explorer.exe - Application ErrorThe application failed to initialize....(more online)

   Another question about security - Hi all,Can anyone explain me this a) on database I create view like this: create view dbo.v_test as select * from TableA b) then I create a sql user named ?Test? with public role to that....(more online)

   SQL Server 2000 SP4 slows DTS job down - Hi chaps,we have just applied Sql Service pack 4 onto out live database.We have a DTS which normally takes about 4 hours, it now takes about 10 hours to complete.We have looked at the AWE hotfix.There are no other processes r....(more online)

   bug#351620, fixed in SP1, back in SP3a - bug#351620 is now present on all 2000sp3a servers, and DTS packages are failing per KP291780. This was fixed in SP1, so... -why's it back, and -how do I fix?appreciate any suggestions, ...my head hurts.......(more online)

   Using top to display records - Hi all, I've a database with 4 fields, my problem is using TOP and getting the right display.the table is built like:Date                Field2 &....(more online)

   Parentheses within a quote - We have a customer name with quotes in it - ABC (After Breakfast Corp.)This didn't work:WHERE (cust_name <> 'ABC (After Breakfast Corp.)')I figure the parentheses are throwing it off, but I can't find anything in manual....(more online)

   CASE in WHERE Clause - I have a where clause that I am trying to write that needs to vary depending on the value of a variable, but I can't seem to get it to work. Is this even possible? I'll paste what I have so far below:WHERE (@Value IS NULL....(more online)

   Slow-running SP - I have a stored procedure that consists of creating two temp tables, joining them and then running SUM's on the data. Everything goes well until the sum's are performed on the table that results from the join. There are 8 S....(more online)

   Filter using count() - Hi guys,i created a script that i want to use generate the resultset based on the value of count(X).ExampleSelect Distinct PD.KPAIDS_NO, PD.LastName, PD.FirstName, PD.Registration_Date, PD.Date_Of_Birth,PD.Marital_S....(more online)

   how do I view foreign keys with t-sql? - I'm a unix guy that needs to do a few things on sql server and I can't figure out how to view all foreign keys on a table with t-sql (NOT em or query analyzer)Anyone have any idea how to do this?....(more online)

   smart headers - Hi, I?m working on the fallowing case: I have to import data form table to text file in SQL server 2000 with stored procedure. Well.. that must be no problem. But my question is: is it impossible to add automaticall....(more online)

   dynamic WHERE clause, Performance issue - I have lots of reports in which there are many fields that are used in dynamic WHERE clause with dynamic sort (ORDER BY field with asc or desc). Following are fields defined in user interface to put required criteria: 1-....(more online)

   count values from two seperate table using join? - trying to do a count on two tables on joins but it's as if both count gives me the same value.can u guys help me out with this one. thxselect p.KPAIDS_NO, count(D.Date_Delivered) as 'Total Current delivery', count(po.Year_Of_....(more online)

   Datum Count - Count of records in Column wise for Null and Actual data - Hi All,I need to know how to write a sttore procedure to find out the COUNT of records having actual value and records with NULL for n Columns of a table.Looping through the Coulms for certian criteriafinding the COUNT for 1 ....(more online)

   Advice for a newbie - Hey guys, not sure if this is the place to ask this question, and if it isn?t I do apologize.  I am almost done with school, and looking to pursue an entry level DBA job.  From what I have seen online at CareerBuild....(more online)

   Workplace Sabatoge or Incompetance. . . - I have been a programer for close to 20 years now. I've recently taken a contracting assignment in which I have run into behavior that I'm trying to determine is either intentional or , through ignorance, sure looks like. I....(more online)

   date within prior year range - I am having a little trouble with a statement to retrieve a count of records that fall within a certain period. I am trying to get the amount of records under 2 years, within 2 - 3 years, 4 - 5 years, 6 - 7 years, and 8 to 10....(more online)

   updating a primary key - This is sort of a weird question I guess, but happened to me the other day. I have a table with just shy of a million records in it, the thing is it has no primary key. So when I go to add the primary key on and ID (GUID....(more online)

   selecting new records - I have two tables that are identical in structure. One has old data, the other has new and old data. How do I select only the records that are not in the old data table?....(more online)

   Teaching the Masses - Day 1 of the PASS Summit was somewhat interesting. I only made a couple sessions, spending most of the time catching up with friends from previous years or people that I email with on a regular basis. It seems like wasted....(more online)

   What Could Go Wrong? - It's the law of unintended consequences: things happening that you don't expect from a seemingly unrelated area. This article talks about a data center having leaks from carpet cleaning above. The concrete floor had aged ....(more online)

   A Fun Poll - Since I've spent most of the week at the PASS Summit, and have been busy meeting with people there instead of working on this site, I opted for a more fun, less thought-requiring poll. Also so many of those people tha....(more online)

   How to deploy Report Builder to User - How do I deploy Report Builder (the RS ad hoc reporting feature) to a user.As an administrator I see the Report Builder icon on the toolbar.I have a test account that I cannot figure out out how to give access to the Report B....(more online)

   For security reasons DTD is prohibited in this XML document - Hello All,   I have a very big Dataset which pulls up millions of rows. When I pass parameters from Windows forms to run on Reportviewer through webservice it is giving me "System.OutOfMemory" Exception and when I p....(more online)

   Show current date in textbox - Hi, I was hoping someone could tell me how I would set the value of a textbox or object to display the current date? I'm guessing I would do it using either vb.net or c#, I'm a newb at both.  Appreciate any hel....(more online)

   Subreports showing an Error - I have a report that uses several supreports. The report runs fine in the development environment. When I run it from the web site it will start showing an error: Subreport could not be shown. This seems to occur at random an....(more online)

   subscription to file share but also notification email - We are trying to limit large emails sent via email through subscriptions and will be changing the subscription to go to the file share instead of email, however, the user still wants to be notified that the report has run and....(more online)

   Restore Database -  Now I've really screwed things up.  I tried to restore the database and received an error, "File already exists".  Now the database is gone and I can't get it back, I always get the same message.I tried creati....(more online)

   Error in Cube Browser SSAS 2005 - hi all, i was finally excited when i got to process the whole cube without the errors.but now im facing another catastrophe!!when i try to browse the processed cube, dragged the gross revenue measure and the hierarchy on the ....(more online)

   DTS Package fails when scheduled but runs when manually run - Using SQL Server 2000 Data Transormation Services (DTS), I have created a package which imports data from an external database (Oracle 10G) via the OLE DB Provider for ODBC. The package executes successfully when run manuall....(more online)

   How to generate ASCII Fixed Length file - I need to generate ASCII Fixed Length fileand be able to specify the position of the columns.For example, CUSTUMER_NAME should start at position 12and end at position 18. How do I do that?DTS generates fixed-length file but t....(more online)

   Data Profiling Tool Suggestion - I am at the design phase of a new data warehouse and am trying to weight the cost benefit of the profiling tools out there.  I know the actual cost will depend on our need and intended use, but I want to find out info wi....(more online)

   sql Cursor - Somthing what i want to doDeclare @var varchar(8000)set @var='select col,col2 from sometable'DECLARE scanprofile CURSOR FAST_FORWARD READ_ONLY FOR@varOPEN scanprofileFETCH NEXT FROM scanprofile -------Is it possible to do........(more online)

   How to copy a certain database table and move the copy to a different database - Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a dif....(more online)

   Link to SQL Server - Hi there from the Newbie! I would like to ask some questions about SQL Server 2005 and Ms Access. In the company I work we have an SQL Server that we enter Data and create some reports. So now I have to create a database in a....(more online)

   A Review of sqlSentry 2.5 - Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/areviewofsqlsentry25.asp....(more online)

   Passing a Table to a Stored Procedure - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3182.asp....(more online)

   XML Workshop VII - Validating values with SCHEMA - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3120.asp....(more online)

   XML Workshop - FOR XML PATH - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3022.asp....(more online)

   Passing a Table to A Stored Procedure - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2977.asp....(more online)

   Experiences from a Real World Project - Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jthomson/3219.asp....(more online)

Reset Identity Columns

By: Anonymous - Posted: 5/24/2007 8:03:10 AM (  (Stand-Alone SQL))

After migrating an Oracle database to SQL 2005, all the identity values were not properly set. This script goes through and sets them all to 1 greater than the max value in the identity column.