Two questions around Date
Question 1: I am passing a datetime as a parameter to a Stored Procedure. The datetime may contain time, which I want to strip out - the date is the important part and it is critical that the time is 00:00. What is the most efficient way of stripping out the time so that it is 0?
Question 2: I have a Year (small integer), Month (tiny integer), and Day (tiny integer). What is the most efficient way to use these to make a datetime?
In both questions, please assume that a universal standard must be used, since the database in question may be running outside of North America.
Many thanks,
Flavelle
for no #1
create proc dateproc
(
@mydate datetime
)
as
select @mydate
, cast(datediff(day,0,@mydate ) as datetime) as dateonly,
@mydate-cast(datediff(day,0,@MYDATE) as datetime)as timeonly
go
declare @date datetime
select @date=getdate()
EXEC dateproc @date
Or perhaps try like this: the only globally unambigous dateformat when you have a date as a string, is YYYYMMDD with no delimiters at all.
declare @year smallint
declare @month tinyint
declare @date tinyint
declare @date1 datetime
select @year = 2005
select @month =1
select @date =2
select @dATE1=
cast(@year as char(4)) +
right('0' + cast(@month as varchar(2)), 2) +
right('0' + cast(@date as varchar(2)), 2)
SELECT @DATE1
/Kenneth
Hello...
To get rid of the Time part in a Date i usually just cast the date to int and then back to datetime (In a Datetime, the Days are represented as Whole number and the time as a fraction....
Example:
declare
@date datetimeselect @date=getdate()
select
cast(cast(@date as int) as datetime)For the conversion from 3 Numbers to an internation date Format i use the same aproach as Kenneth. But I usually prefer not to store a date in a string. I try to convert it as soon as possible to a "real" Datetime. If you keep you dates as strings you will run into some problems with conversions sooner or later. So try to move the Data to the type its really is.
To all - thanks.
I like the approach of casting the date to int (it's simple, and certainly never something that I would have thought of). I also like Kenneth's approach to converting 3 numbers to an international date string. In my code, I convert everything to dates immediately and it seems to work very well, so I don't keep things as strings for any length of time. Sounds dumb, but you would think that I am not the first to want to have a consistent way of formatting three numbers as dates. Need to use error checking to ensure that the three numbers will convert to a valid date, but my questions have been answered, with many thanks!!!
Flavelle
The function above, select cast(cast(@date as int) as datetime), is not working correct.
For 2006-12-08 16:14:53.827 it returns 2006-12-09 00:00:00.000 (for 2006-12-13 10:37:35.690 it returns 2006-12-13 00:00:00.000)
Nele:
Thanks for finding the examples. The following appears to provide correct results, where TestDate is replaced with a date parameter or the name of a date field (depending on where you are using it. The 112 specifies that the date is to be converted into character format using ISO, which is YYYYMMDD:
CAST(CONVERT (char(8), TestDate, 112) AS datetime)
Regards,
Flavelle
