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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Date Only field in sql server 2005

Author  Topic 

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-01 : 00:29:20
i want to store only date in a table in sql server 2005. if i use smalldatetime datatype, then i am getting date and time both.
is there any datatype of type Date Only in sql server 2005 ?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-06-01 : 00:50:14
Datatype to store Only date is introduced in SQL server 2008.
In 2005 you need to use datetime or smalldatetime (depending upon your requirement).
You can format the datefield and remove the time component before storing the date.
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-06-01 : 00:51:14
And in case you are doing formatting to remove the time component, try to do the same in front end.
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-01 : 00:56:19
quote:
Originally posted by pk_bohra

Datatype to store Only date is introduced in SQL server 2008.
In 2005 you need to use datetime or smalldatetime (depending upon your requirement).
You can format the datefield and remove the time component before storing the date.


Actually what i am trying to do is, i am using crystal reports 2008 to generate reports from sql table. here i am using a parameter which is mapped to the datetime field of sql table. i want to use only date in this parameter but i can do so only if the field in sql table is also date only.
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-01 : 01:05:28
and i am afraid i'll not be able to upgrade to sql server2008. is there another solution for this problem ?
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-01 : 04:00:01
AnyBody Help please
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-06-01 : 06:52:58
With whatever experience i have with Crystal report, i think that you can pass the date parameter from front end (crystal report) to datetime parameter in SQL.

In case you are worried that in the data in the table may have timepart also then you can remove the time part using the below statement.

DATEADD(dd, DATEDIFF(dd,0, <your date field>), 0).
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-02 : 03:17:31
quote:
Originally posted by pk_bohra

With whatever experience i have with Crystal report, i think that you can pass the date parameter from front end (crystal report) to datetime parameter in SQL.

In case you are worried that in the data in the table may have timepart also then you can remove the time part using the below statement.

DATEADD(dd, DATEDIFF(dd,0, <your date field>), 0).



please help me here....
if i select parameter of type Date in crystal reports then i am not able to map it with the field of type smalldatetime in sql table.

but if select parameter of type Date Time in crystal report then its ok. but i want date only. please help
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-02 : 10:37:31
PLease Help !!!!
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-06-02 : 10:42:20
I think the only way would be to use a varchar field and then convert to datetime wherever you need to use it.

PBUH
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2010-06-02 : 10:55:05
If you send '20050101'( or '01/01/2005') in as a parameter, sql will assume the time piece is 00:00:00. If that's what's in your database then you're good to go. You can also explicitly convert it into a date inside your stored procedure. The dateadd(dd,datediff(day,0,yourDate),0) will strip of the time part of the in the sql table. You can also account for it in your stored porcedure in the where clause

WHERE yourDate >= @inputDate and yourDate < dateadd(day,1,@inputDate)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-02 : 14:18:50
quote:
Originally posted by Idera

I think the only way would be to use a varchar field and then convert to datetime wherever you need to use it.

PBUH

but if i use a parameter of type varchar in crystal reports then i won't be able to compare dates with it.
Go to Top of Page

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts
Posted - 2010-06-02 : 14:20:35
Did you see Jim's reply. Whats the problem with stripping the time part off with dateadd(dd,datediff(day,0,yourDate),0) or using the yyyyMMdd format.
Go to Top of Page

rohit04413
Yak Posting Veteran

72 Posts
Posted - 2010-06-02 : 14:25:46
quote:
Originally posted by jimf

If you send '20050101'( or '01/01/2005') in as a parameter, sql will assume the time piece is 00:00:00. If that's what's in your database then you're good to go. You can also explicitly convert it into a date inside your stored procedure. The dateadd(dd,datediff(day,0,yourDate),0) will strip of the time part of the in the sql table. You can also account for it in your stored porcedure in the where clause

WHERE yourDate >= @inputDate and yourDate < dateadd(day,1,@inputDate)

Jim

Everyday I learn something that somebody else already knew

may that'll work with asp.net but i am talking about crystal reports. if i create a parameter of type Date in crystal reports and the then try to select records based on the value of this parameter, i am simply not able to do this because when i go to select record section of report then i select the field of sql table, select comparison operator ,when i try to select the parameter created above, its not there in the list because parameter is of type Date and the sql field is of type smalldatetime.
i want the report's parameter to be date only but there is no datatype of Date in sql server.

Solution?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-06-02 : 17:11:01
This sounds like a problem with Crystal Reports, not SQL Server. It is easy to do what you want in SQL Server.

You should post your question on a Crystal Reports support forum.





CODO ERGO SUM
Go to Top of Page

mandm
Posting Yak Master

120 Posts
Posted - 2010-06-02 : 18:00:32
From what I recall of Crystal reports you can create a stored procedure that supplies the data and whatever formatting you want to do to the columns then tie the report to that instead.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-06-03 : 05:50:57
One solution is, goto toolbar in the Report file

Report-->Selection Forumulas-->Record

In the code window, type

{@your_date_parameter}=Date(your_small_datetime_column)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-12-04 : 02:15:05
quote:
Originally posted by pk_bohra

And in case you are doing formatting to remove the time component, try to do the same in front end.

how can be this happens i am using 2005 any suggestions

With Regards
Kashyap M
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-12-07 : 02:19:17
quote:
Originally posted by kashyap_sql

quote:
Originally posted by pk_bohra

And in case you are doing formatting to remove the time component, try to do the same in front end.

how can be this happens i am using 2005 any suggestions

With Regards
Kashyap M

select dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site