January 3, 2013 at 10:44 pm
Hi all,
I have the following:
Insert into table(col A)
Select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) As ColA
from table B
I thought this select statement will return me a current date value with no time, instead it returned me 4/1/2013 12:00:00 AM. I guess its due to the data type of my column A. I tried choosing datetime and smalldatetime, both doesnt help. what should i do if i want my column A to be of a date datatype with values as 4/1/2013?
Help appreciated
January 3, 2013 at 11:23 pm
There is no DATE datatype in SQL Server 2005, but it is available in SQL Server 2008 and above, so if upgrading is an option, that is one way to go.
By convention you can store a date only in a DATETIME column with the time set to 00:00:00.000, which is what statement you supplied does.
January 3, 2013 at 11:43 pm
Michael Valentine Jones (1/3/2013)
There is no DATE datatype in SQL Server 2005, but it is available in SQL Server 2008 and above, so if upgrading is an option, that is one way to go.By convention you can store a date only in a DATETIME column with the time set to 00:00:00.000, which is what statement you supplied does.
thanks for replying, upgrading is not an option....finding alternative way.
January 4, 2013 at 7:28 am
10e5x (1/3/2013)
Michael Valentine Jones (1/3/2013)
There is no DATE datatype in SQL Server 2005, but it is available in SQL Server 2008 and above, so if upgrading is an option, that is one way to go.By convention you can store a date only in a DATETIME column with the time set to 00:00:00.000, which is what statement you supplied does.
thanks for replying, upgrading is not an option....finding alternative way.
What is wrong with just using a DATETIME or SMALLDATETIME datatype? They have the advantage of not allowing bad dates, and all of the SQL Server date manipulation functions work with them.
January 4, 2013 at 8:00 am
Agreed. Your best option is to use datetime. I would strongly urge you not to do something you will regret like using a varchar for datetime. If you don't want to see the time value when viewing the data that is a formatting issue. You can either use convert to remove the time portion or even better, do your formatting in the front end.
_______________________________________________________________
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/
January 4, 2013 at 8:12 am
Sean Lange (1/4/2013)
Agreed. Your best option is to use datetime. I would strongly urge you not to do something you will regret like using a varchar for datetime. If you don't want to see the time value when viewing the data that is a formatting issue. You can either use convert to remove the time portion or even better, do your formatting in the front end.
Strongly agree with this as well.
January 4, 2013 at 8:49 am
So if i have an sql statement in my execute sql task that looks something like this:
Delete from TABLE where (CREATION_D = ?)
CREATION_D is the column i am discussing about. So if i use it as datetime it will give me values such as 1/4/2013 12:00:00 AM while ? is a parameter from my package variable (ExecDate) of a datatype varchar that will look like 1/4/2013 11:38:00 AM. I want that delete statement to be triggered when both are of same date. How shall i edit to achieve this? Thanks for all the inputs, i decided to heed all advice and remain my CREATION_D as datetime datatype
January 4, 2013 at 8:51 am
10e5x (1/4/2013)
So if i have an sql statement in my execute sql task that looks something like this:
Delete from TABLE where (CREATION_D = ?)CREATION_D is the column i am discussing about. So if i use it as datetime it will give me values such as 1/4/2013 12:00:00 AM while ? is a parameter from my package variable (ExecDate) of a datatype varchar that will look like 1/4/2013 11:38:00 AM. I want that delete statement to be triggered when both are of same date. How shall i edit to achieve this? Thanks for all the inputs, i decided to heed all advice and remain my CREATION_D as datetime datatype
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, 0, DATEDIFF(DAY, 0, ?? )));
January 4, 2013 at 8:54 am
Cadavre (1/4/2013)
10e5x (1/4/2013)
So if i have an sql statement in my execute sql task that looks something like this:
Delete from TABLE where (CREATION_D = ?)CREATION_D is the column i am discussing about. So if i use it as datetime it will give me values such as 1/4/2013 12:00:00 AM while ? is a parameter from my package variable (ExecDate) of a datatype varchar that will look like 1/4/2013 11:38:00 AM. I want that delete statement to be triggered when both are of same date. How shall i edit to achieve this? Thanks for all the inputs, i decided to heed all advice and remain my CREATION_D as datetime datatype
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, 0, DATEDIFF(DAY, 0, ?? )));
Actually:
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, ?? ), 0));
January 4, 2013 at 8:58 am
Lynn Pettis (1/4/2013)
Actually:
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, ?? ), 0));
SET NOCOUNT ON;
DECLARE @a DATETIME;
SET @a = GETDATE();
--First
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, @a ));
--Second
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a ), 0);
/* RESULTS
-----------------------
2013-01-04 00:00:00.000
-----------------------
2013-01-04 00:00:00.000
*/
Do you return something different to me? Or were you expressing a preference?
January 4, 2013 at 9:12 am
omg i am losing the discussion over here... so the recommended solution to me is which? And is it a typo or? why there are two '??' sry for the noobness and confusion
January 4, 2013 at 9:15 am
10e5x (1/4/2013)
omg i am losing the discussion over here... so the recommended solution to me is which? And is it a typo or? why there are two '??' sry for the noobness and confusion
Either would work, I think Lynn was just showing you a different way to achieve the result. The two "?" is for your parameter.
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @yourDateParamater )));
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, @yourDateParamater ), 0));
Another way would be to do this: -
DELETE FROM TABLE WHERE (CREATION_D = CAST(DATEDIFF(DAY, 0, @yourDateParamater ) AS DATETIME));
I think they're all roughly equivalent in terms of performance.
January 4, 2013 at 9:33 am
thanks alot for the solutions. I shall test run it later. Appreciated, both of u.
January 4, 2013 at 10:16 am
Cadavre (1/4/2013)
Lynn Pettis (1/4/2013)
Actually:
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, ?? ), 0));
SET NOCOUNT ON;DECLARE @a DATETIME;
SET @a = GETDATE();
--First
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, @a ));
--Second
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a ), 0);
/* RESULTS
-----------------------
2013-01-04 00:00:00.000
-----------------------
2013-01-04 00:00:00.000
*/
Do you return something different to me? Or were you expressing a preference?
Syntax, the second value provided in the DATEADD is a number, not the date:
DATEADD (datepart , number , date )
January 4, 2013 at 10:24 am
Cadavre (1/4/2013)
Lynn Pettis (1/4/2013)
Actually:
DELETE FROM TABLE WHERE (CREATION_D = DATEADD(DAY, DATEDIFF(DAY, 0, ?? ), 0));
SET NOCOUNT ON;DECLARE @a DATETIME;
SET @a = GETDATE();
--First
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, @a ));
--Second
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a ), 0);
/* RESULTS
-----------------------
2013-01-04 00:00:00.000
-----------------------
2013-01-04 00:00:00.000
*/
Do you return something different to me? Or were you expressing a preference?
Using dates instead of relying on the implicit conversion of 0 to 1900-01-01:
SET NOCOUNT ON;
DECLARE @a DATETIME;
SET @a = GETDATE();
--First
SELECT DATEADD(DAY, CAST('19000101' AS DATETIME), DATEDIFF(DAY, CAST('19000101' AS DATETIME), @a ));
GO
SET NOCOUNT ON;
DECLARE @a DATETIME;
SET @a = GETDATE();
--Second
SELECT DATEADD(DAY, DATEDIFF(DAY, CAST('19000101' AS DATETIME), @a ), CAST('19000101' AS DATETIME));
GO
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply