GetDate() Formatting And Functions Documentation

Aug 20, 2006

I'm trying to do something very simple here but I keep getting stuck becuase I can't find much on getDate() in the documenation. Where, in the documenation, does it talk about truncating times, adding to times and all that good stuf.

Below is what I'm trying to do here: I have a while loop that adds to the starting hour of 6am 15 min until it gets to like 7pm. I do realize at this point that just adding 15 is suppsed to add 15 days based on what I have read, but I'm getting an error when I parse this and since I can't seem to find the docs I don't know what to do next?

Msg 102, Level 15, State 1, Procedure PopulateDatabase, Line 32

Incorrect syntax near '@TeeTime'.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Przemek
-- Create date:
-- Description:
-- =============================================

ALTER PROCEDURE [dbo].[PopulateDatabase]
-- Add the parameters for the stored procedure here


AS
declare @CourseID uniqueIdentifier
declare @TeeTimeSlot int
declare @TeeTime dateTime
BEGIN
SET NOCOUNT ON;

--Course 1 *******************************************************************

INSERT INTO
Course (CourseID, Name, Address, PhoneNumber)
VALUES
(NewID(), 'Prospect Lake', '123 Prospect St', 2508129832)

SET @CourseID = (SELECT CourseID FROM Course WHERE Name = 'Prospect Lake')
SET @TeeTimeSlot = 0
SET @TeeTime = '6:00'

WHILE @TeeTimeSlot < 56
BEGIN
INSERT INTO
Schedule (ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)
VALUES
(NewID(), @CourseID, getDate(), @TeeTime, Rand(5))
@TeeTime = @TeeTime + 1
END
END


 

View 4 Replies


ADVERTISEMENT

Getdate() Formatting

Dec 10, 2007

I am trying to convert getdate() into a certain varchar(10) format. What I want it to look like is 2007-12-30. However, I can't seem to find the proper code to make it do this. Is there one? The closest I could find was 105.

DELETE FROM [<database>].[<user>].[<table>]
WHERE <column> < convert(varchar, getdate() - 14, 105)

View 8 Replies View Related

Getdate() Formatting Prob

Jun 23, 2006

i am sure this post appears here a lot...

my date column currently looks like this:

2006-06-23 00:00:00.000

i want it to look like this:

2006-06-23

my code (a stored procedure) looks like this


Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(50) = NULL,
@photo_default VARCHAR(50) = NULL,
@photo_private VARCHAR(50) = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(varchar(10), getdate(), 101))
END

BEGIN

--SET ANSI_WARNINGS OFF

INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)

VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)

--SET ANSI_WARNINGS ON

END

RETURN



what am i doing wrong?

thanks.

View 2 Replies View Related

Getdate() >= Startdate And Getdate() <= Enddate

Oct 4, 2000

Please i need an exmple of ur solution, thanks :)

I'm using some files to show certain pages on certain date for an example

File name : aa.doc
start date: 10/02/00
end date : 10/03/00

But it expires on 10/02/00, here is the strored procedure:

Before the date comes, it expires the page
Here is my stored procedure:

"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "

Thankx a lot

View 1 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

SQL Server 2005: CLR Functions Vs SQL Functions

May 26, 2006

I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID

View 9 Replies View Related

Documentation

Jul 28, 2004

What information should I be documenting about a server (SQL) and about the databases found on that server. I was task with this job. Please help.

Thanks

Lystra

View 2 Replies View Related

Documentation

Nov 13, 2004

I'd like to go through and document the databases I'm responsible for...it's probably a good practice and I'm sure there a several approaches to doing so.

Anybody have comments, recommendations, or possibly a nice word template that links everything up?

Thanks for the input.

Alex

View 1 Replies View Related

Documentation

Mar 9, 2004

hello everyone,
I have to document about four databases, has anyone got any specific format or tool for documenting a database?
Any inputs would be very helpful
regards,
Harshal.

View 13 Replies View Related

Xml Documentation

Jul 26, 2007

Will there ever be xml code documentation support inside of Sql Server projects?

View 1 Replies View Related

Where Is SQLDMO Documentation?

May 5, 2008

Hi all
Could anyone tell me from where i can find complete Documentation for SQLDMO Object and how can i initialize
and use it in my programms?

Kind Regards.

View 1 Replies View Related

Sql Documentation Tool

Dec 3, 2005

Does anyone knows a good FREE tool for documenting a DB?

so far i'm with ApexSQL but its not free, wondering if anyone knows a free tool.

Thanks,

View 1 Replies View Related

SQL Database Documentation

Nov 9, 2004

Hi guys,
Can any one tell me about any Database Documentation Tool for SQL server.
Please reply asap.

Junaid

View 5 Replies View Related

MS SQL Utilities For Documentation

Oct 16, 1998

I am looking for good utilities that document MS SQL table schema, indexes, layouts etc. I am currently looking at SQL auditor, but this product does not give me table schema or any kind of device revisions. I recently was given several SQL servers that have not been documented in any way.

thanks

ps SQL auditor gets a B+ in my book. Let me know what you think

View 2 Replies View Related

Documentation For Queries

Sep 20, 2005

Hallo !

Right now I am looking for the query-code to delete a table (not only the rows, but all of it). Somebody could give me the code?

In general I am always looking for a place on the web, where all sql-server 2005 (express) query-functions are explained. Somebody knows the right place?

Thank you very much!

Regards,
Fabain

View 6 Replies View Related

Best Documentation Tool?

Jun 13, 2007

Can anyone advise me on what they regard as the best SQL Server documentation tool. I am currently looking at Redgate and ApexSQL products. Do any of you have experience with these or others? I need one that will document 2000 and 2005 databases.

Thanks.

View 15 Replies View Related

Trigger Documentation

Jun 28, 2007

I am documenting a database.

When I view dependencies on a table I can see a view- so I go to views and copy the information.
But when there is a trigger as a dependency- I cannot find where to go to view it.

Are triggers within stored procedures somewhere? Or do they have their own separate place somewhere?

As you can see- I don’t know much about SQL…

Thanks

View 3 Replies View Related

But The Documentation Is NOT Correct

Jul 23, 2005

The documentation saysISNUMERIC returns 1 when the input expression evaluates to a validinteger, floating point number, money or decimal type; otherwise itreturns 0. A return value of 1 guarantees that expression can beconverted to one of these numeric types.(Cut and pasted from books online)Yet the following, one of many, example shows this is not true.select isnumeric(char(9))select convert(int, char(9))-----------1(1 row(s) affected)Server: Msg 245, Level 16, State 1, Line 2Syntax error converting the varchar value '' to a column of data typeint.So, besides filtering every possible invalid character, how do youconvert dirty values without error. I am not concerned that I may loosepossibly valid values or convert suspect strings to 0 (zero). I justwant to run without raising an error

View 3 Replies View Related

Documentation To Get Started

Jul 20, 2005

What is the best online documentation either on the Web or installedwithSQL Server that I could use to get started? I want to jump in andstartcreating tables, etc for applications.Also, what is the most recommended text for converting my DBAexperience from other database to SQL Server.ThanksBill.

View 3 Replies View Related

SSIS Documentation

Apr 17, 2007

Hi folks

According to the book I am reading at the moment, there's supposed to be an SSIS documentation that ships with the product. I had the software installed by the IT guys but nothing with it apart from the books on line.

Does any one know of specific documentation on this?



Regards

Baldev

View 3 Replies View Related

C2 Auditing Documentation

Sep 20, 2007

Hi everyone,

I am to try to find documentation about C2 auditing into SQL Server 2005. The only one doc I fand about it is:
SQL 2000 C2 Admin and User Guide
It cover SQL Server 2000 and Windows NT4.0.

I do not find any document who cover the security from SQL Server 2005.

Can you help me please?

Thx.

Laurent

View 1 Replies View Related

Documentation Tools For SSB?

Oct 27, 2006

Does anyone know if there are automated documentation tools around that will produce docs for Service Broker objects?

Thanks,

AndrewM

View 1 Replies View Related

Need ActiveX Documentation, Please

Oct 11, 2007

Hi,

Is there a source I can go to for ActiveX documentation? I just need to be able to translate some things.

For example:
On Error Resume Next


What does this do within the ActiveX script?

Thanks

View 5 Replies View Related

Mapping Documentation

Mar 21, 2007

I need to generate a document with the fields mapping from a DataFlow Task. Anybody knows how to do this?

Know I'm using screenshots from the destination object mapping.

Thanks

View 5 Replies View Related

External Documentation

Sep 12, 2005

I know that the BI studio can build a nice flow document that is associated with the package.  Is there an easy way to build a series fo documents that can be shared with other users without having them go into Visual Studio?

View 15 Replies View Related

Documentation Of The Tables In SQL Server

Jul 17, 2007

Hi all
I have 15 tables in my database and I want to create a pdf file with description about all the columns of these tables(Data Model)
Few questions
How can I get all the column names in the excel or pdf file?
Is there any tool which can give me the output template for these columns and then I just have to fill the description for each column?
Thanks

View 10 Replies View Related

CHECK And CONSTRAINT Documentation

Jun 4, 2006

I am having some difficulties to find the documentation relative to the syntax of CHECK and CONTRAINT in MS SQL Server 2005. Can someone points me toward a reference guide?

Thanks in advance,
Joannès
http://www.peoplewords.com

View 1 Replies View Related

Table Definition Documentation

Dec 1, 2006

Hosam writes "Dear Team

Im new to SQL server 2005, and I wonder if I can create the database documentation, I mean table schema and attributes as word or excel file.

Is there anyone can help me please

Thanks
Hosam"

View 1 Replies View Related

CHECKDB Error Documentation

Mar 7, 2007

Hi,

I have a question for Paul Randal. I was perusing the forums earlier and I came across a post where you mentioned that you had documented what each of the errors that CHECKDB returns means, but you did not link to this documentation. Perhaps I am just stupid, but I was unable to find this anywhere. Could you point me in the direction for this when you get a chance? I'd be interested in both the 2000 documentation and the 2005 documentation if you have it updated yet. Thanks.


Larry Taylor
Database Administrator
SchooleOne

View 2 Replies View Related

System Schema && Documentation

Jul 20, 2005

Anyone know how to query sysobjects & syproperties showing the table,field name, and field properties (specifically the description)? Ihave so far:SELECT *FROM sysproperties spINNER JOIN sysobjects soON sp.id = so.idSELECT *FROM syscolumns scINNER JOIN sysobjects soON sc.id = so.idWHERE so.type = 'U'....not sure how to join the two. The 'ID' column refers to the tableID and not the field ID.

View 4 Replies View Related

SQL Server 2008 Documentation

Jun 15, 2007

Hi,

Can you please tell me links to SQL Server 2008 documentation on MSDN? I read SQL Server 2008 CTP is released today. I'm very much interested to know more about it. Thanks in advance.



Cheers,

Gopi

mgopinath.blogspot.com

View 1 Replies View Related

Ssis Package Documentation

Mar 28, 2008

i'm looking for a good method in order to document my ssis packages. i already tried this ssis documenter http://www.ssisdocumenter.com/ but was not very satisfied with it. what are your experiences with documenting ssis packages?

View 9 Replies View Related

ExternalActivator Sample - Documentation

Sep 21, 2006

Hi!

Where can I find some documentation about the ExternalActivator sample available on gotdotnet.com? I'm looking for a tutorial how the sample can be set up and used...

Thanks

Klaus Aschenbrenner
www.csharp.at
http://www.sqljunkies.com/weblog/klaus.aschenbrenner

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved