December 2, 2015 at 10:23 am
How would I convert a date field that outputs, for example, Dec 1 2015 12:00AM into 12/01/2015? I need to use it in a date range.
December 2, 2015 at 10:26 am
cory.bullard76 (12/2/2015)
How would I convert a date field that outputs, for example, Dec 1 2015 12:00AM into 12/01/2015? I need to use it in a date range.
If it it truly a date column you don't need to convert anything. I suspect however from your example that it isn't a date column? If it is not a datetime you can cast/convert it easily enough but you should consider changing the datatype to store datetime information in a datetime datatype.
https://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
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/
December 2, 2015 at 10:35 am
It maybe a datetime....but, I'd like to take that field and run the prior days data. Could I do that with a field that displays data in that format?
December 2, 2015 at 10:47 am
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results
December 2, 2015 at 10:47 am
cory.bullard76 (12/2/2015)
It maybe a datetime....but, I'd like to take that field and run the prior days data. Could I do that with a field that displays data in that format?
Columns do not display data in a format. Is the column a datetime? Can it be reliably converted to a datetime if it isn't?
Generally speaking for this type of query you would do something along these lines.
where YourColumn >= dateadd(day, datediff(day, 0, GETDATE()) - 1, 0)
AND YourColumn < dateadd(day, datediff(day, 0, getdate()), 0)
This is using a number of the date functions from this great post. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
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/
December 2, 2015 at 10:49 am
cory.bullard76 (12/2/2015)
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results
That is because it is a datetime which also has the time value. So a row where the time is 8am is NOT equal to value with no time. You can cast your column as a DATE or see the code I posted above.
_______________________________________________________________
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/
December 2, 2015 at 11:38 am
Sean Lange (12/2/2015)
cory.bullard76 (12/2/2015)
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 resultsThat is because it is a datetime which also has the time value. So a row where the time is 8am is NOT equal to value with no time. You can cast your column as a DATE or see the code I posted above.
You also need to use single quotes, otherwise it'll perform 2 integer divisions which will finally result in 0. Zero will be converted into date 01/01/1900.
You're statement could be like this:
field = '12/01/2015'
Or even better, using the ISO format which won't depend on language settings:
field = '20151201'
Or as Sean said, using a range.
field >= '20151201' AND field < '20151202'
December 2, 2015 at 12:53 pm
cory.bullard76 (12/2/2015)
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results
Where somedatetimefield >= '2015-12-01 00:00:01'
and somedatetimefiled <= '2015-12-01 23:59:59'
December 2, 2015 at 1:07 pm
yakko_Warner (12/2/2015)
cory.bullard76 (12/2/2015)
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results
Where somedatetimefield >= '2015-12-01 00:00:01'
and somedatetimefiled <= '2015-12-01 23:59:59'
Careful. With datetime, the maximum value for the time component in a given day is 23:59:59.997, so that will miss any row from that day that is greater than 23:59:59.000 and less than or equal to 23:59:59.997, as well as any row with a time component between 00:00:00.000 and 00:00:00.997. While that may be a fairly small risk depending on your data, the fix is so easy there's no point not to be safe. It's safest to just use midnight of the next day as an exclusive upper bound with 'less than', and midnight of the desired day as an inclusive lower bound with 'greater than or equal to'.
CREATE TABLE #TestDateTime (somedatetimefield DATETIME);
INSERT INTO #TestDateTime
VALUES
('2015-12-01 00:00:00.997'),
('2015-12-01 13:33'),
('2015-11-30 18:47'),
('2015-12-01 23:59:59.5');
--Misses the first and fourth rows above
SELECT * FROM #TestDateTime
Where somedatetimefield >= '2015-12-01 00:00:01'
and somedatetimefield <= '2015-12-01 23:59:59';
--This is safer, and also isn't dependent on the minimum and maximum values
--for the datatype used (datetime or datetime2, for example).
SELECT * FROM #TestDateTime
Where somedatetimefield >= '20151201'
and somedatetimefield < '20151202';
DROP TABLE #TestDateTime;
Cheers!
December 2, 2015 at 1:53 pm
Thanks Jacob, That's exactly what i was trying to figure out.
I wasn't sure if you left the time portion off if that would work.
However as your awesome response is very very helpful and educational at the same time.
December 3, 2015 at 10:12 am
yakko_Warner (12/2/2015)
cory.bullard76 (12/2/2015)
Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results
Where somedatetimefield >= '2015-12-01 00:00:01'
and somedatetimefiled <= '2015-12-01 23:59:59'
You can also try:
Where cast(somedatetimefield as date) = '2015-12-01'
Don Simpson
December 4, 2015 at 12:22 pm
A CAST on the database column would likely preclude the use of an available index, while using the native values would allow index usage, if available. The >= and < pair is the best choice.
December 4, 2015 at 12:29 pm
PhilPacha (12/4/2015)
A CAST on the database column would likely preclude the use of an available index, while using the native values would allow index usage, if available. The >= and < pair is the best choice.
You're right. However, casting a datetime column to date, still allows an index to be used. I'm not sure why would it happen like that, but it does. You could test it if you want.
December 4, 2015 at 12:33 pm
Thanks for the update about datetime --> date; I wasn't aware of that.
December 4, 2015 at 12:57 pm
PhilPacha (12/4/2015)
Thanks for the update about datetime --> date; I wasn't aware of that.
I just wanted to include a test because people shouldn't just trust anything they read online. 😉
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL
DROP TABLE #SomeTestTable
;
--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartDate DATETIME,
@EndDate DATETIME,
@Days INT --This is the "range"
;
--===== Preset the variables to known values
SELECT @NumberOfRows = 1000000,
@StartDate = '20150101', --Inclusive
@EndDate = '20200101', --Exclusive
@Days = DATEDIFF(dd,@StartDate,@EndDate)
;
--===== Create the test table with "random constrained" integers and floats
-- within the parameters identified in the variables above.
SELECT TOP (@NumberOfRows)
SomeRandomDateTime = RAND(CHECKSUM(NEWID())) * @Days + @StartDate
INTO #SomeTestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Create an index on the datetime column
CREATE INDEX IXDateDatetime ON #SomeTestTable(SomeRandomDateTime);
;
--===== Show ten rows of the table
SELECT *
FROM #SomeTestTable
WHERE CAST(SomeRandomDateTime as date) = CAST(GETDATE() AS date);
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply