April 10, 2013 at 10:40 pm
--===== 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
April 10, 2013 at 11:04 pm
Was there a question?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 10, 2013 at 11:07 pm
K, I need to change the format of the Date
April 11, 2013 at 7:52 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2013 at 9:22 am
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:
idMM/DD/4 digit yearMM/DD 2 digit yearGerman 2 digit yearGerman 4 digit year
410/17/2007 10/17/07 17.10.0717.10.2007
3710/17/2007 10/17/07 17.10.0717.10.2007
For a rather complete outline of what you can do can be found at:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply