Date problem in SQL Server

Hi

Good day!

I am having some problem with the date-time datatype. I want to store only date in my table in this format - DD-MM-YYYY - and I do not need time. This must be compatible with the format of Visual Basic.

How to achieve this?

SQL Server datetime datatype stores both DATE and TIME. [datetime] has a presicion to milliseconds, and [shortdatetime] has a precision to minutes.

If you do NOT provide the TIME component, it will default to midnight (or 12:00:00 AM). It is very common for applications to ignore the time component. If you do not need the TIME component, use a shortdatetime datatype.

Refer to Books Online about [datetime] and [shortdatetime] datatypes.

|||

what is the problem in storing date and time. is it because of Storage size or is it because u wnat to show only date to the user. If you want to show only date.... this formating can be done at the frontend. Don't change the datatype... you store the data as datetime only and do the formating at the frontend. And also check CONVERT/CAST function in BOL.

Madhu

|||

You cannot do this with SQL Server 2005 and previous release. In Katmai (next release), we define several new datetime data types and you will be able to do it.

Or you can use the techniques mentioned by the previous posts.

Thanks.

|||

Hi Madhu

My problem is actually in queiry. When I use the type date/time and I assign a default value to GETDATE() in sql server, it stores both date and time. But when I run sproc with where clause like - WHERE Date = @.Date - where the time is not given, I can't find the date!

How to solve it? I am using VB to run the sproc.

Regards

Kapalic

|||

in this case you may need to use CONVERT Function of SQL Server. Suppose u r passing date mm/dd/yyyy then

Where convert(varchar(10),Date,101)='04-10-2007'

Madhu

|||

That is because when you are using @.Date without a time portion, it defaults to midnight (12:00:00.000), and you don't have any rows with that exact time.

Try the following WHERE clause:

Code Snippet

WHERE ( [Date] >= @.Date

AND [Date] < ( dateadd( day, 1, @.Date )

)

This assumes that @.Date is in the form of 'mm/dd/yyyy', and that you want all values from midnight until the last possible moment before the next midnight (Or [ >= 12:00:00.000 ] AND [ < 11:59:59.997 ]

(You don't really want to follow Madhu's suggestion of using convert(varchar(10), Date, 101) BECAUSE it would invalidate the use of an index and could be very inefficient.)

|||

Hi Arnie,

Thnk you very much for your reply. The problem is, I want to use the date format as DD/MM/YYYY. This I forgot to mention. And I want to link the sproc with a DateTimePicker control in Visual Basic.

Please resque...

Regards

Kapalic

|||

Read about CONVERT Function in BOL ... what u need to do is ... convert the date in sql to which ever format u required. the below statement will do that

if the supplied date is 04/10/2007 dd/mm/yyyy run the below mentioned statement and see teh result

Where convert(varchar(10),Date,105)='04-10-2007'

Madhu

|||

Using a VB date format of 'DD/MM/YYYY', and using a DateTimePicker is NOT a problem as long as you are using a Date datatype in VB, and the procedure call is using a SQL Datetime datatype.

If your VB date variable it used when you make the SQL Stored procedure call, the WHERE clause example above will work just fine.