is there any Date only format available in sql ser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

is there any Date only format available in sql ser

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-20 : 11:17:35

hello All,

i am using sql server 2005, is there any data type just DATE (without time), because i have a column it needs to store only date value, because when i am processig this column in ssis (integration services) i am getting some problems (if it has time in it)

so simple is there any format available (for column data types) which stores only dates no time stamp

thanks in advance
Best Regards
asin

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-20 : 11:24:54

select convert(char(8),getdate(),112)

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-20 : 11:30:46

quote:
Originally posted by hanbingl

select convert(char(8),getdate(),112)

Hi hanbingl,

thank you for your reply,

can you please let me know is there any column data type available for only Date.

in your example we have time but we are trying to eliminate tim,e part right?,

what i want is to store just date and without any expressions (or conversions) i want to display the date

ex: select hiredate from employee --> should display the only date (also it should store only date) is there any way that we can do in sql server

once again thanks for your reply

Best Regards
asin

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-10-20 : 11:32:12

There is no date data type in SQL 2005, hanbingls' query will turn your date into a string. The below will kock off the time portion for you, while preserving the date format

declare @date datetime

set @date = getdate()
select dateadd(d,datediff(d,0,@date),0)

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 11:36:13

use jimf's query to strip off time part. Its not advisable to covert to varchar for striping time. Also if you're by any chance upgrading to sql 2008, it has a datatype called date storing only datevalues.

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-20 : 20:07:12

quote:
Originally posted by visakh16

use jimf's query to strip off time part. Its not advisable to covert to varchar for striping time. Also if you're by any chance upgrading to sql 2008, it has a datatype called date storing only datevalues.

Hello All,

thanks for your replies

in my package process i am reading data from source table and then writing to CSV file

in my source sql server table, DueDate column has data format as M/DD/YYYY HH:MM:SS AM (Ex 1/30/2008 12:00:00 AM)

how can i convert the data to "YYYY-DD-MM" format (ex: 2008-01-30 )

i trried to use with derived columns but i am getting many errors

can any one please let me know

if in sql server table it is YYYY-MM-DD format then we can get it by using substring but in sql server data format is different

can any one please....

Thanks in advance

asini

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 23:40:04

is DueDate a datetime field?

kutumbarao
Starting Member

13 Posts

Posted - 2008-10-21 : 01:24:56

Hi Dhani,

Use following example query while insert into database.
Ex: SELECT SUBSTRING('1/30/2008 12:00:00 AM',1,CHARiNDEX(' ','1/30/2008 12:00:00 AM')-1)

It will inset the due date in the table with “YYYY-MM-DD” format.(Due Date must be Datetime field)

Thanks,
KutumbaRao.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 01:29:47

quote:
Originally posted by kutumbarao

Hi Dhani,

Use following example query while insert into database.
Ex: SELECT SUBSTRING('1/30/2008 12:00:00 AM',1,CHARiNDEX(' ','1/30/2008 12:00:00 AM')-1)

It will inset the due date in the table with “YYYY-MM-DD” format.(Due Date must be Datetime field)

Thanks,
KutumbaRao.

but it will still have time part as 00:00:00 which is 12 AM

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-21 : 10:33:22

quote:
Originally posted by visakh16

is DueDate a datetime field?

Hello Visakh,

yes this is Datetime field in Sql server

Thanks & Regards
asini

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-21 : 10:36:23

quote:
Originally posted by kutumbarao

Hi Dhani,

Use following example query while insert into database.
Ex: SELECT SUBSTRING('1/30/2008 12:00:00 AM',1,CHARiNDEX(' ','1/30/2008 12:00:00 AM')-1)

It will inset the due date in the table with “YYYY-MM-DD” format.(Due Date must be Datetime field)

Thanks,
KutumbaRao.

Hi Kutumba Rao,

thanks for your reply,
but it is not worked as per eliminating time part

================== as Rafael it can be achieve as below
use the below expression like this in a derived column:

(DT_STR,4,1252)YEAR(<<dateColumn>>) + "/" +(DT_STR,2,1252)DAY(<<dateColumn>>) + "/" + (DT_STR,2,1252)MONTH(<<dateColumn>>)

=================
the above function working fine but it is showing DD or MM part as single digit if it ha value less than 10 (i.e 2008-7-9) i need it to be as (2008-07-09)

Regards
asini

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-21 : 12:17:38

Hello All,

thanks for all of your replies

i got it work with some extension of Rafael Solution

so finally it was worked with below expression in derived column

(DT_STR,4,1252)YEAR([Due Date]) + "-" + (LEN((DT_STR,2,1252)MONTH([Due Date])) == 1 ? "0" + (DT_STR,2,1252)MONTH([Due Date]) : (DT_STR,2,1252)MONTH([Due Date])) + "-" + (LEN((DT_STR,2,1252)DAY([Due Date])) == 1 ? "0" + (DT_STR,2,1252)DAY([Due Date]) : (DT_STR,2,1252)DAY([Due Date]))

Once again Thank you for all, for your valuable replies

Regards

asin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources