Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I am getting learn to post,am i correct now??????? What will be your reply for this??????? Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 10:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 5, 2013 11:56 PM
Points: 12, Visits: 29
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME,
Value MONEY,
YearValue INT,
Monthvalue INT
)

--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value, YearValue, Monthvalue)
SELECT '4','Oct 17 2007 12:00AM',5.1709,'8','1' UNION ALL
SELECT '37','Oct 17 2007 12:00AM',5.5319,'17','8' UNION ALL
SELECT '44','Oct 17 2007 12:00AM',5.5793,'21','11' UNION ALL
SELECT '54','Oct 17 2007 12:00AM',5.2471,'9','2' UNION ALL
SELECT '55','Oct 17 2007 12:00AM',5.1177,'7','0' UNION ALL
SELECT '81','Oct 17 2007 12:00AM',5.5510,'18','9' UNION ALL
SELECT '86','Oct 17 2007 12:00AM',5.5128,'16','7' UNION ALL
SELECT '96','Oct 17 2007 12:00AM',5.5758,'20','10'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
Post #1441105
Posted Wednesday, April 10, 2013 11:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,904, Visits: 2,846
Was there a question?

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1441108
Posted Wednesday, April 10, 2013 11:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 5, 2013 11:56 PM
Points: 12, Visits: 29
K, I need to change the format of the Date
Post #1441110
Posted Thursday, April 11, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
andrewalex.r (4/10/2013)
K, I need to change the format of the Date


???

Did you have a question or did you just want to post some data to see if it posted correctly?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441259
Posted Thursday, April 11, 2013 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 5,601, Visits: 25,005
andrewalex.r (4/10/2013)
K, I need to change the format of the Date


You can change the format for displaying the date using the convert function. For example:
SELECT id,CONVERT(VARCHAR(10), DateValue, 101)AS 'MM/DD/4 digit year',CONVERT(VARCHAR(10), DateValue, 1) AS 'MM/DD 2 digit year'
,CONVERT(VARCHAR(10), DateValue, 4) AS 'German 2 digit year'
,CONVERT(VARCHAR(10), DateValue, 104) AS 'German 4 digit year' FROM #mytable

Results:

id MM/DD/4 digit year MM/DD 2 digit year German 2 digit year German 4 digit year
4 10/17/2007 10/17/07 17.10.07 17.10.2007
37 10/17/2007 10/17/07 17.10.07 17.10.2007

For a rather complete outline of what you can do can be found at:

http://www.sql-server-helper.com/tips/date-formats.aspx



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1441326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse