I dont want datatime datatype ...just date
-
With some help from folks on this site, I have been able to create a dts package that imports a .txt into a staging table then moves the data into a table with the proper data types. It's working nicely.
My table is dropped and created each time it runs. The date fields are created as dataType: datetime.
I don't want the "time" just the "date" either 8/3/04 or 08/03/04 or 8/3/2004 any of these are fine.
I tried to change the sql that creates the table to use datatype: Date (this created an error). then I tried shortdatetime and the error was "out of range".
It runs without error if i use datetime but it gives me the time that I don't want.
Thank you for any info you can offer.
Paul
-
It's not as easy as you might expect. Here's a bit of SQL that might get you heading in the right direction:
select getdate() as datetime, cast(floor(cast(getdate() as float)) as datetime) as date
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
SQL 2000 doesn't have a date datatype, you'll have to wait for SQL Server 2005 for that.
When you insert the date into a datetime field it will automatically add 00:00:00 as the time.
The following will show the date portion from a datetime, but again if you insert the result into a datetime field it'll add the time.
SELECTgetdate()
, CONVERT(varchar(12), GetDate(), 101)
, CONVERT(varchar(12), GetDate(), 103)
, CONVERT(varchar(12), GetDate(), 111)
, CONVERT(varchar(12), GetDate(), 112)
, Left(GetDate(), 11)
--------------------
Colt 45 - the original point and click interface
-
Hmm...
you could store it as an integer also:
cast(someintfield as smalldatetime)
ex:
select 1, cast(1 as smalldatetime)
1 1900-01-02 00:00:00
You can format the time part away for a datetime or smalldatetime field (use convert instead of cast), but, like Excel, you cannot keep SS from storing it, because the underlying datatype is a floating point value, with the integrand being the days since 1/1/1900 (or something like that), and the decimal portion being the % of time from midnight.
-
ou can create your field as nvarchar and save your dates in mm/dd/yyyy format if you are in the UK or dd/mm/yyyy if you are in the USA. You could still use date functions with this setup.
Azzam.
-
I recommend that you continue storing it in a DateTime field. Use the Convert function when retreiving the value from the database.
Something Like....
Select Convert(VarChar(12), MyDateField, 101) As MyReturnedFieldName From MyTableName
I also recommend that you use the smalldatetime instead. The smalldatetime field uses less storage space, but lacks some of the precision. According to BOL... "Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute."
A DateTime field uses 8 bytes while a SmallDateTime uses 4 bytes.
I hope this helps.
George Mastros
Orbit Software, Inc. -
-- I use a user defined function to strip the time part out of the Datetime --
CREATE FUNCTION dbo.TodaysDate (@fDate datetime)
RETURNS varchar(10) AS
BEGIN
Return (Convert(varchar(10),@fDate,101))
END
-- I use the function in stored procedures and triggers like this one.
-- BTW, LastModifiedOn is a Datetime field
CREATE TRIGGER [ModifyTrend] ON dbo.SomeTable
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE SomeTable
SET LastModifiedBy = suser_sname(), LastModifiedOn = dbo.TodaysDate(GETDATE())
FROM inserted
WHERE SomeTable.RowID = inserted.RowID
SET NOCOUNT OFF
-
Why not to use computed columns?
create table #t
(
d datetime,
d1 as convert(datetime, convert(char(10), d, 103), 103)
 
create index I1 on #t(d1)
/* by setting an index on a computed columns you can "materialze" it */
insert #t (d) values (getdate())
insert #t (d) values (getdate())
insert #t (d) values (getdate())
select * from #t where d1 = '20040805'
/*here are the results */
d d1
------------------------ ------------------------
2004-08-05 16:55:36.763 2004-08-05 00:00:00.000
2004-08-05 16:55:37.403 2004-08-05 00:00:00.000
2004-08-05 16:55:37.920 2004-08-05 00:00:00.000
Bye
Gabor -
Why can't you use a user defined data type for this..??
Just try it...Execute this script in Query Analyser.
or u can create all these things mannually.
/****************************************************************************/
/* Creating user defined datetype */
EXEC sp_addtype DateOnly, 'char(10)'
GO
/* Creating Rule */
Create RULE FirstRule
AS @id LIKE '[0-3][0-9]/[0-1][0-2]/[1-9][0-9][0-9][0-9]'
GO
/* Binding rule to DateOnly DataType */
sp_bindrule FirstRule, 'DateOnly'
GO
/* Creating Table with user defined datatype DateOnly */
CREATE TABLE DateOnlyTable
(
Code INT PRIMARY KEY,
JoinDate DateOnly, /* User Defined DataType */
TypeName VARCHAR(50),
)
GO
/* Trying to insert values to Table */
INSERT INTO DateOnlyTable VALUES(1, '04/01/2004','CKShaiju')
/* Checking the data inserted or not */
SELECT * FROM DateOnlyTable
/* Check these insert statements also
INSERT INTO DateOnlyTable VALUES(1, '04/01/2004 00:00:000','CKShaiju') -- Error: Time Added
INSERT INTO DateOnlyTable VALUES(2, '04/01/200','CKShaiju') -- Error : year not correct
INSERT INTO DateOnlyTable VALUES(4, '04/01A2004','CKShaiju') -- Error : Slash replaced with A
INSERT INTO DateOnlyTable VALUES(5, '41/01/2004','CKShaiju') -- Error : Day started with 41
INSERT INTO DateOnlyTable VALUES(6, '28/13/2004','CKShaiju') -- Error : Month is 13
INSERT INTO DateOnlyTable VALUES(6, '28/07/0004','CKShaiju') -- Error : Year started with 0
You can get some more errors in this case.
So you have to add necessary validations in Rule.
*/
/****************************************************************************/
Regards C.K.Shaiju.
_____________________________________________
One ounce of practice is more important than tonnes of dreams -
I have these function which may also help u
http://www.sqlservercentral.com/scripts/contributions/733.asp
Date without Time (2 functions)
Some time you need to know only Date without Time.
This is simple function that allows you truncate datetime field to date only.
This function can help you to construct dynamic T-SQL for select date period.
P.S. Thanks for ispaleny!
Conversion only for real not enough! It is necessary convert to real that correct errors near the last instant of the day.
--------------------------------------------
CREATE FUNCTION DateOnly_sdt
(@DateAndTime AS smalldatetime)
RETURNS smalldatetime
AS
/*****************************************
* return date without time smalldatetime
*****************************************/
BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS smalldatetime)
END
CREATE FUNCTION DateOnly_dt
(@DateAndTime AS datetime)
RETURNS datetime
AS
/*****************************************
* return date without time for datetime
*****************************************/
BEGIN
RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS datetime)
END
another Function
----------------------------------------------
CREATE function getdatePortion(@inDate datetime)
returns datetime
As
begin
return convert(datetime,convert(varchar(11),@inDate))
end
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Try this....
SELECT CAST(DATEPART(hh, GETDATE()) AS VARCHAR(2)) + ':' + CAST(DATEPART(mi, GETDATE()) AS VARCHAR(2))+ ':' + CAST(DATEPART(ss, GETDATE()) AS VARCHAR(2))+ ':' + CAST(DATEPART(ms, GETDATE()) AS VARCHAR(3))
Result
------------
6:56:29:803
(1 row(s) affected)
Regards
CK Shaiju.
_____________________________________________
One ounce of practice is more important than tonnes of dreams -
The original poster asked for a way of retrieving just the date part of a datetime field. Your method returns exactly the opposite - the time part - in a complex fashion! If you do want to output the time bit, this does the same job:
select right(convert(varchar(50),getdate(),13),12)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply