October 17, 2011 at 4:33 am
Hello,
I'm trying to return data for the current month(October 2011) but i cant seem to get it to work. Anyone what i am doing wrong here?
declare @livedate datetime
set @livedate = GETDATE()
select
item
,date1
,fcst1
,fcst2
,fcst3
from dbo.SJ
where date1 = @livedate
CREATE TABLE SJ
(
item varchar(50),
date1 datetime,
fcst1 int NOT null,
fcst2 int NOT null,
fcst3 int NOT null
);
drop table dbo.SJ
-- Delete and recreate the table (quicker than a standard delete)
truncate table dbo.sj
-- Insert data into table
insert into dbo.SJ
values('A101','20110101',1,2,3);
insert into dbo.SJ
values('A102','20110101',57,48,87);
insert into dbo.SJ
values('A103','20110101',15,28,33);
insert into dbo.SJ
values('A104','20110101',11,22,33);
insert into dbo.SJ
values('A105','20110101',19,29,31);
insert into dbo.SJ
values('A106','20110101',13,26,32);
insert into dbo.SJ
values('A107','20110101',16,23,34);
insert into dbo.SJ
values('A108','20110101',18,25,30);
insert into dbo.SJ
values('A109','20110101',17,234,345);
insert into dbo.SJ
values('A110','20110101',174,283,3983);
-- Feb
insert into dbo.SJ
values('A111','20110201',1,2,3);
insert into dbo.SJ
values('A112','20110201',57,48,87);
insert into dbo.SJ
values('A113','20110201',15,28,33);
insert into dbo.SJ
values('A114','20110201',11,22,33);
insert into dbo.SJ
values('A115','20110201',19,29,31);
insert into dbo.SJ
values('A116','20110201',13,26,32);
insert into dbo.SJ
values('A117','20110201',16,23,34);
insert into dbo.SJ
values('A118','20110201',18,25,30);
insert into dbo.SJ
values('A119','20110201',17,234,345);
insert into dbo.SJ
values('A120','20110201',174,283,3983);
-- March
insert into dbo.SJ
values('A121','20110301',13,22,34);
insert into dbo.SJ
values('A122','20110301',574,485,873);
insert into dbo.SJ
values('A123','20110301',154,284,334);
insert into dbo.SJ
values('A124','20110301',114,224,333);
insert into dbo.SJ
values('A125','20110301',198,298,319);
insert into dbo.SJ
values('A126','20110301',133,266,322);
insert into dbo.SJ
values('A127','20110301',164,238,340);
insert into dbo.SJ
values('A128','20110301',181,252,304);
insert into dbo.SJ
values('A129','20110301',175,232,341);
insert into dbo.SJ
values('A130','20110301',179,288,373);
--April
insert into dbo.SJ
values('A131','20110401',13,22,34);
insert into dbo.SJ
values('A132','20110401',571,481,871);
insert into dbo.SJ
values('A133','20110401',151,281,331);
insert into dbo.SJ
values('A134','20110401',111,221,331);
insert into dbo.SJ
values('A135','20110401',191,291,311);
insert into dbo.SJ
values('A126','20110401',131,261,321);
insert into dbo.SJ
values('A127','20110401',161,231,341);
insert into dbo.SJ
values('A128','20110401',182,251,301);
insert into dbo.SJ
values('A129','20110401',171,231,340);
insert into dbo.SJ
values('A130','20110401',171,281,371);
--May
insert into dbo.SJ
values('A141','20110501',13,22,34);
insert into dbo.SJ
values('A142','20110501',574,484,874);
insert into dbo.SJ
values('A143','20110501',154,284,334);
insert into dbo.SJ
values('A144','20110501',114,224,334);
insert into dbo.SJ
values('A145','20110501',194,294,314);
insert into dbo.SJ
values('A146','20110501',134,264,324);
insert into dbo.SJ
values('A147','20110501',164,234,344);
insert into dbo.SJ
values('A148','20110501',184,254,305);
insert into dbo.SJ
values('A149','20110501',174,234,344);
insert into dbo.SJ
values('A150','20110501',174,284,374);
--June
insert into dbo.SJ
values('A161','20110601',13,22,34);
insert into dbo.SJ
values('A162','20110601',575,485,875);
insert into dbo.SJ
values('A163','20110601',155,285,335);
insert into dbo.SJ
values('A164','20110601',115,225,335);
insert into dbo.SJ
values('A165','20110601',195,295,315);
insert into dbo.SJ
values('A166','20110601',135,265,325);
insert into dbo.SJ
values('A167','20110601',165,235,345);
insert into dbo.SJ
values('A168','20110601',185,255,354);
insert into dbo.SJ
values('A169','20110601',175,235,345);
insert into dbo.SJ
values('A170','20110601',175,285,375);
--July
insert into dbo.SJ
values('A181','20110701',13,22,34);
insert into dbo.SJ
values('A182','20110701',576,486,876);
insert into dbo.SJ
values('A183','20110701',156,286,336);
insert into dbo.SJ
values('A184','20110701',116,226,336);
insert into dbo.SJ
values('A185','20110701',196,296,316);
insert into dbo.SJ
values('A186','20110701',136,267,326);
insert into dbo.SJ
values('A187','20110701',166,236,346);
insert into dbo.SJ
values('A188','20110701',186,256,306);
insert into dbo.SJ
values('A189','20110701',176,236,346);
insert into dbo.SJ
values('A190','20110701',176,286,376);
-- whats currently in the table
--August
insert into dbo.SJ
values('A191','20110801',17,27,37);
insert into dbo.SJ
values('A192','20110801',577,487,877);
insert into dbo.SJ
values('A193','20110801',157,287,337);
insert into dbo.SJ
values('A194','20110801',117,227,337);
insert into dbo.SJ
values('A195','20110801',197,297,317);
insert into dbo.SJ
values('A196','20110801',137,267,327);
insert into dbo.SJ
values('A197','20110801',167,237,347);
insert into dbo.SJ
values('A198','20110801',187,257,307);
insert into dbo.SJ
values('A199','20110801',177,237,347);
insert into dbo.SJ
values('A200','20110801',177,277,377);
-- Sep
insert into dbo.SJ
values('A201','20110901',18,28,38);
insert into dbo.SJ
values('A202','20110901',578,488,878);
insert into dbo.SJ
values('A203','20110901',158,288,338);
insert into dbo.SJ
values('A204','20110901',118,228,338);
insert into dbo.SJ
values('A205','20110901',198,298,318);
insert into dbo.SJ
values('A206','20110901',138,268,328);
insert into dbo.SJ
values('A207','20110901',168,238,348);
insert into dbo.SJ
values('A208','20110901',188,258,308);
insert into dbo.SJ
values('A209','20110901',178,238,348);
insert into dbo.SJ
values('A210','20110901',178,288,378);
-- Oct
insert into dbo.SJ
values('A211','20111001',10,20,30);
insert into dbo.SJ
values('A212','20111001',570,480,870);
insert into dbo.SJ
values('A213','20111001',150,280,330);
insert into dbo.SJ
values('A214','20111001',110,220,330);
insert into dbo.SJ
values('A215','20111001',190,290,310);
insert into dbo.SJ
values('A216','20111001',130,260,320);
insert into dbo.SJ
values('A217','20111001',160,230,340);
insert into dbo.SJ
values('A218','20111001',180,250,300);
insert into dbo.SJ
values('A219','20111001',170,230,340);
insert into dbo.SJ
values('A220','20111001',170,280,370);
-- Nov
insert into dbo.SJ
values('A221','20111101',19,29,39);
insert into dbo.SJ
values('A222','20111101',579,489,879);
insert into dbo.SJ
values('A223','20111101',159,289,339);
insert into dbo.SJ
values('A224','20111101',119,229,339);
insert into dbo.SJ
values('A225','20111101',199,299,319);
insert into dbo.SJ
values('A226','20111101',139,269,329);
insert into dbo.SJ
values('A227','20111101',169,239,349);
insert into dbo.SJ
values('A228','20111101',189,259,309);
insert into dbo.SJ
values('A229','20111101',179,239,349);
insert into dbo.SJ
values('A230','20111101',179,289,37);
-- Dec
insert into dbo.SJ
values('A241','20111201',23,22,24);
insert into dbo.SJ
values('A242','20111201',594,495,893);
insert into dbo.SJ
values('A243','20111201',194,294,394);
insert into dbo.SJ
values('A244','20111201',194,294,393);
insert into dbo.SJ
values('A245','20111201',198,298,399);
insert into dbo.SJ
values('A246','20111201',193,296,392);
insert into dbo.SJ
values('A247','20111201',194,298,390);
insert into dbo.SJ
values('A248','20111201',191,292,394);
insert into dbo.SJ
values('A249','20111201',195,292,391);
insert into dbo.SJ
values('A250','20111201',199,298,393);
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 17, 2011 at 4:44 am
Jnrstevej (10/17/2011)
Hello,I'm trying to return data for the current month(October 2011) but i cant seem to get it to work. Anyone what i am doing wrong here?
declare @livedate datetime
set @livedate = GETDATE()
select
item
,date1
,fcst1
,fcst2
,fcst3
from dbo.SJ
where date1 = @livedate
Your query is only going to return data from the exact date and time that you execute the code - GETDATE() returns the current date and time.
Try something like this: -
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
That looks for everything where the date1 is greater than or equal to "2011-10-01 00:00:00.000" and less than "2011-11-01 00:00:00.000" - so all of October.
October 17, 2011 at 5:21 am
Brilliant thanks for that.
Another question how would i control the dates that i want to show and increment it by 12 months according to the current.
For example, if its October 11 show the following
Dec 11
Jan 12
Feb 12
Mar 12
Apri 12
May 12
June 12
July 12
Aug 12
Sep 12
Oct 12
Nov 12
-- I was thinking along the lines of this
declare @livedate datetime
set = DATEADD(MONTH, DATEDIFF(MONTH, +12, GETDATE()), 0)
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 = @livedate
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 17, 2011 at 5:46 am
Jnrstevej (10/17/2011)
Brilliant thanks for that.Another question how would i control the dates that i want to show and increment it by 12 months according to the current.
For example, if its October 11 show the following
Dec 11
Jan 12
Feb 12
Mar 12
Apri 12
May 12
June 12
July 12
Aug 12
Sep 12
Oct 12
Nov 12
Wouldn't 12 months from October include November 2011?
I've coded it to what you've asked for, if that's not correct then change the numbers.
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
October 17, 2011 at 7:33 am
Yes your right it should be from October and include Nov 12 miss that out
The adjustments you have made is perfect the other 2 questions i have is the following
Question 1
How do i show decrement months instead. For example the last 12 months,
Start at Oct 11
Sep11,
Aug 11,
July 11,
June 11,
May 11,
April 11,
March 11,
Feb 11,
January 11,
Dec 10
Nov 10
Oct 10
Question 2
Instead of using the 'where clause' how can i declare the section below as a variable instead
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
Something along these lines
declare @livedate datetime
set @livedate WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 17, 2011 at 8:01 am
Jnrstevej (10/17/2011)
Yes your right it should be from October and include Nov 12 miss that outThe adjustments you have made is perfect the other 2 questions i have is the following
Question 1
How do i show decrement months instead. For example the last 12 months,
Start at Oct 11
Sep11,
Aug 11,
July 11,
June 11,
May 11,
April 11,
March 11,
Feb 11,
January 11,
Dec 10
Nov 10
Oct 10
Question 2
Instead of using the 'where clause' how can i declare the section below as a variable instead
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
Something along these lines
declare @livedate datetime
set @livedate WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
OK, rather than continuing to spoon-feed you the answer I'm going to take another approach. I was hoping that you'd use the previous code as examples and work through how to customise it for your needs, as this hasn't happened I'm going to give you some examples and hope you can work it out from there. If you have any further questions, please don't be discouraged from asking, I just don't think that spoon-feeding you more answers is helping you.
Here's an example to help you work out the answer to your first question: -
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) --the 1st of last month
UNION ALL SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) --the 1st of this month
UNION ALL SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) --the 1st of next month
If you execute the above, you'll hopefully see how to change the statement to display whatever number you're after. Here's another example of the same principle using a tally/numbers table.
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
postally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM t4 x, t4 y),
negtally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * (-1) AS negnum
FROM t4 x, t4 y),
tally AS (SELECT num
FROM (SELECT negnum AS num
FROM negtally
UNION ALL
SELECT num
FROM postally) a)
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + num, 0) --Gives you the start of the month from 1st Jan 1753 to 1st Feb 7473
FROM tally
WHERE num >= -3105
ORDER BY num
Your second question can be answered in a few ways.
Here's one: -
DECLARE @livedate VARCHAR(MAX)
SET @livedate = 'date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT item,date1,fcst1,fcst2,fcst3 FROM dbo.SJ'
SET @SQL = @SQL + CHAR(32) + 'WHERE' + CHAR(32) + @livedate
EXEC(@SQL)
Here's another: -
DECLARE @startLiveDate DATETIME, @endLiveDate DATETIME
SET @startLiveDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)
SET @endLiveDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 14, 0)
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 >= @startLiveDate AND date1 < @endLiveDate
And a third: -
DECLARE @startLiveDate INT, @numberOfDates INT
SET @startLiveDate = -10
SET @numberOfDates = 12
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @startLiveDate, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + (@startLiveDate + @numberOfDates), 0)
All of the above is untested, but hopefully you'll get the idea.
October 17, 2011 at 8:18 am
OK, rather than continuing to spoon-feed you the answer I'm going to take another approach.
That's the right approach. Good for OP 🙂
October 17, 2011 at 9:57 am
Jnrstevej (10/17/2011)
Thanks @cadavre for your time and effort I'm currently working through this now once I'm done I'll post my results
No problem. As I said, if you have more questions then please ask.
Here's a quick test environment I set-up to try and illustrate the sort of task you're trying to do. In this case, the task is to get a count of the records between two particular dates.
First, here's the code to set-up the environment.
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME) AS date1
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Add a Primary Key
ALTER TABLE #testEnvironment
ADD CONSTRAINT testEnvironment_PK_ID
PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100
Now we'll do some queries.
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
---------------------------------------
--Actual query we're looking at
---------------------------------------
SELECT COUNT(*),
MIN(date1) AS FromDate,
MAX(date1) AS ToDate
FROM #testEnvironment
---------------------------------------
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== COUNT 12 MONTHS FROM TODAY =========='
SET STATISTICS TIME ON
---------------------------------------
--Actual query we're looking at
---------------------------------------
SELECT COUNT(*),
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FromDate,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0) AS ToDate
FROM #testEnvironment
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0)
---------------------------------------
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== COUNT PREVIOUS 12 MONTHS FROM TODAY =========='
SET STATISTICS TIME ON
---------------------------------------
--Actual query we're looking at
---------------------------------------
SELECT COUNT(*),
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) AS FromDate,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS ToDate
FROM #testEnvironment
WHERE date1 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND date1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
---------------------------------------
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
October 17, 2011 at 1:34 pm
I've made slight adjustments to your sample code which has given me the results that i require. I must admit date functions are not one of my strong points but the material that you have provided me which I've spent a few hour reading has definitely helped me understand the logic behind it.
Once again thanks for taking the time to prepare this material for me and the time spent, it has been much appreciated. 🙂
[Code]
-- What i will be using now
DECLARE @startLiveDate DATETIME, @endLiveDate DATETIME
SET @startLiveDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 0, 0)
SET @endLiveDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -14, 0)
SELECT item
,date1
,fcst1
,fcst2
,fcst3
FROM dbo.SJ
WHERE date1 <= @startLiveDate AND date1 > @endLiveDate
order by date1 desc
[/code]
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
October 18, 2011 at 2:02 pm
Maybe someone sugested this and I missed it but just convert getdate() to a string and back to a date.
select CONVERT(datetime,(convert(char(10),getdate(),101))) as Today
October 18, 2011 at 2:10 pm
jshahan (10/18/2011)
Maybe someone sugested this and I missed it but just convert getdate() to a string and back to a date.select CONVERT(datetime,(convert(char(10),getdate(),101))) as Today
If you search ssc you may find several other threads where this is discussed. It has been determined that converting to and from a character string is slower.
October 18, 2011 at 8:45 pm
jshahan (10/18/2011)
Maybe someone sugested this and I missed it but just convert getdate() to a string and back to a date.select CONVERT(datetime,(convert(char(10),getdate(),101))) as Today
Hopefully, you'll change your mind about that after today 😉 Please run the test code below and see why...
--===== Setup a million row test table in a nice safe place that everyone has
USE TempDB;
GO
SELECT TOP 1000000
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2; --Lack of join criteria makes this a CROSS-JOIN
GO
SET STATISTICS TIME ON;
GO
PRINT '--===== DATEADD/DATEDIFF ==========================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== DOUBLE CONVERT ============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(NVARCHAR, SomeDate,103), 103)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== CONVERT/DATEDIFF ==========================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, datediff(day, 0, SomeDate))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== CAST/DATEDIFF =============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CAST(datediff(day, 0, SomeDate) AS DATETIME)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== jshahan method =============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(datetime,(convert(char(10),SomeDate,101)))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
SET STATISTICS TIME OFF;
--===== Housekeeping
DROP TABLE dbo.JBMTest;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2011 at 9:26 pm
Jeff Moden (10/18/2011)
jshahan (10/18/2011)
Maybe someone sugested this and I missed it but just convert getdate() to a string and back to a date.select CONVERT(datetime,(convert(char(10),getdate(),101))) as Today
Hopefully, you'll change your mind about that after today 😉 Please run the test code below and see why...
--===== Setup a million row test table in a nice safe place that everyone hasUSE TempDB;
GO
SELECT TOP 1000000
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2; --Lack of join criteria makes this a CROSS-JOIN
GO
SET STATISTICS TIME ON;
GO
PRINT '--===== DATEADD/DATEDIFF ==========================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== DOUBLE CONVERT ============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(NVARCHAR, SomeDate,103), 103)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== CONVERT/DATEDIFF ==========================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, datediff(day, 0, SomeDate))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== CAST/DATEDIFF =============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CAST(datediff(day, 0, SomeDate) AS DATETIME)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
PRINT '--===== jshahan method =============================================================================='
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(datetime,(convert(char(10),SomeDate,101)))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
SET STATISTICS TIME OFF;
--===== Housekeeping
DROP TABLE dbo.JBMTest;
GO
Yeah - hmm...use anything but the double convert methods;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 19, 2011 at 8:58 am
Since you mentioned you are not very familiar with date functions... Check out this pdf: http://www.dotnet4all.com/snippets/factsheet%20SQL%20Server.pdf
This helped me a lot when I was learning, and I still use it for reference from time to time.
Thanks,
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply