How to format DateTime?
-
I really need your all expertise!
Current SQL Server store date as DateTime or Small Datetime which Date and Time cannot be separated.
I need to compare the Date with 2 user input date. The SQL is similar as below.
SELECT <Table Fields>
FROM <Table>
WHERE <Sample Date> BETWEEN <User Input Data1> AND <User Input Date2>
I find it is hard for me to format the compare the Sample Date with the User Input Dates. The SQL fail to compare the date format.
How to format the User Input Dates in order to similar with Sample Date? And I only want to compare the date only, i don't need the time.
Can somebody help.
Thank in advanced.
-
just get rid of the time part on the input and sample dates.
Method1
casting as int
Cast(@inputDate1 as int)
Method2
remove time part
@newinputdate = Cast(Convert(varchar(10),@inputDate, 101) as datetime)
Method3
you could use acombination of DateDiff using 'd' as the difference
HTH
* Noel -
You can use CONVERT.
WHERE CONVERT(VARCHAR(10),sampledate,121) BETWEEN CONVERT(VARCHAR(10),userdate1,121) AND CONVERT(VARCHAR(10),userdate2,121)
Basically this converts the date and time to the format (121) of yyyy-mm-dd.
Refer to the Books OnLine for CONVERT and CAST.
-SQLBill
-
quote:
I really need your all expertise!Current SQL Server store date as DateTime or Small Datetime which Date and Time cannot be separated.
I need to compare the Date with 2 user input date. The SQL is similar as below.
SELECT <Table Fields>
FROM <Table>
WHERE <Sample Date> BETWEEN <User Input Data1> AND <User Input Date2>
I find it is hard for me to format the compare the Sample Date with the User Input Dates. The SQL fail to compare the date format.
How to format the User Input Dates in order to similar with Sample Date? And I only want to compare the date only, i don't need the time.
Can somebody help.
Thank in advanced.
-
[Try to use these formats:
select convert(char(8),getdate(),112) as yyyymmdd
yyyymmdd
--------
20031113
select convert(char(8),getdate(),108) as [hh:mm:ss]
hh:mm:ss
--------
15:11:47
See you
Rubens]
I really need your all expertise!
Current SQL Server store date as DateTime or Small Datetime which Date and Time cannot be separated.
I need to compare the Date with 2 user input date. The SQL is similar as below.
SELECT <Table Fields>
FROM <Table>
WHERE <Sample Date> BETWEEN <User Input Data1> AND <User Input Date2>
I find it is hard for me to format the compare the Sample Date with the User Input Dates. The SQL fail to compare the date format.
How to format the User Input Dates in order to similar with Sample Date? And I only want to compare the date only, i don't need the time.
Can somebody help.
Thank in advanced.
[/quote]
-
For most purposes I just take advantage of what default time is for a user input date.
between UserinputDate1 and
dateadd(hour,24,UserinputDate2)
-
To get a proper date comparison you need to do two conversions not one. First convert to varchar to strip off the time and then convert back to datetime for the comparisson.
Your statement would be something like,
SELECT <Table Fields>
FROM <Table>
WHERE CONVERT(datetime, CONVERT(varchar(10), <Sample Date>, 106))
BETWEEN CONVERT(datetime, CONVERT(varchar(10), <User Input Data1>>, 106))
AND CONVERT(datetime, CONVERT(varchar(10), <User Input Date2>>, 106))I use type 106 on the convert statement as it returns the date in the format 'dd mmm yyyy'. Using the three character month avoids any conversion issues between US/UK/etc... date formats.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 11/13/2003 3:46:58 PM
--------------------
Colt 45 - the original point and click interface
-
If user input dates have no times then
SELECT <Table Fields>
FROM <Table>
WHERE <Sample Date> >= <User Input Data1>
AND <Sample Date> < DATEADD(d,1,<User Input Date2>)
Far away is close at hand in the images of elsewhere.
Anon. -
/*Create this user defined function on your database. Your query would then be:
SELECT <Table Fields>
FROM <Table>
WHERE <Sample Date> BETWEEN dbo.f_GetBaseDate(<User Input Data1>) AND dbo.f_GetBaseDate(<User Input Date2>)
*/
CREATE FUNCTION dbo.f_GetBaseDate ( @varDate varchar(20) )
RETURNS varchar(20)
AS
BEGIN
DECLARE @varRET varchar(20)
IF ISDATE(@varDate) = 1
BEGIN
--We strip out the time stamp.
--If returned to a DateTime datatype,
--the time defaults to 12:00:00 AM
SELECT @varRET = CONVERT(varchar,MONTH(@varDate)) + '/'
+ CONVERT(varchar,DAY(@varDate)) + '/'
+ CONVERT(varchar,YEAR(@varDate))
END
RETURN (@varRET) --returns a null value, if not a date
END
-
I have something in place similar to Renee's solution, accept I like hers better. I put a function in every database and granted execute to public on the function. It is amazing how much this is used.
"Keep Your Stick On the Ice" ..Red Green -
For use within code, just use:
CONVERT(char(8),DateCol,112)This will be much faster than a UDF and doesn't really require much more typing.
And it usually does not make sense to use BETWEEN with datetimes when you're stripping the times from the parameters. Use David's method of >= the first date and < the second date + 1. Otherwise you'll not return rows with dates on the final day unless they have no time component.
Also try and avoid using functions on the table data (e.g. SQLBill), as that will prevent use of an index on the column if one is created.
--Jonathan
--Jonathan -
For ur information, the Userdate 1 and Userdate2 are enter by user in the format of "dd-mm-yyyy", Therefore, the query will look similar as below;
SELECT .......
FROM .......
WHERE.......
AND CONVERT(varchar(20), s.u_sampledt, 106) BETWEEN ('17-Sep-2003') AND ('18-Sep-2003')
ANDCONVERT(datetime, CONVERT(varchar(10), s.u_sampledt, 106)) BETWEEN CONVERT(datetime, CONVERT(varchar(10), ('17-09-2003'), 106)) AND CONVERT(datetime, CONVERT(varchar(10), ('18-09-2003'), 106))
As I run the query, i get the error message as shown in below;
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How should I solve this problem?
-
Valid date conversions depend on many factors including the language setting of the connecting user. The error will occur is you try to convert dmy date when sql expects mdy. Putting the following at the top of your proc should solve your problem.
SET DATEFORMAT dmy
Far away is close at hand in the images of elsewhere.
Anon. -
There two things I can see that are a problem,
1) You are doing two comparissons that are effectively the same thing.
CONVERT(varchar(20), s.u_sampledt, 106) BETWEEN ('17-Sep-2003') AND ('18-Sep-2003')
AND CONVERT(datetime, CONVERT(varchar(10), s.u_sampledt, 106)) BETWEEN CONVERT(datetime, CONVERT(varchar(10), ('17-09-2003'), 106)) AND CONVERT(datetime, CONVERT(varchar(10), ('18-09-2003'), 106))
2) The 'Type' parameter has no meaning when convert a varchar to datetime, it only has meaning when converting datetime to varchar. So in the second BETWEEN statment you're converting '18-09-2003' to datetime. As you only have numbers you'll need to use the SET DATEFORMAT statement to ensure correct interpretation of the days and months.
If you can't provide the character version of the month in the date parameter, then you'll need to use SET DATEFORMAT. If you don't use the time component of the u_sampledt field (ie: it only has something like 2003-17-09 00:00:00.000 in the table) then you don't need to worry about using CONVERT.
eg
SET DATEFORMAT dmy
SELECT .......
FROM .......
WHERE.......
AND s.u_sampledt
BETWEEN '17-09-2003' AND '18-09-2003'Would equate to something like,
SELECT .......
FROM .......
WHERE.......
AND 2003-09-17 00:00:00.000
BETWEEN 2003-09-17 00:00:00.000
AND 2003-09-18 00:00:00.000However if you do have times, (ie: it has something like 2003-09-09 21:34:14.115 in the table), then you will need to use CONVERT.
SET DATEFORMAT dmy
SELECT .......
FROM .......
WHERE.......
AND CONVERT(datetime, CONVERT(varchar(20), s.u_sampledt, 106))
BETWEEN CONVERT(datetime, CONVERT(varchar(20), CONVERT(datetime, '17-09-2003'), 106))
AND CONVERT(datetime, CONVERT(varchar(20), CONVERT(datetime, '18-09-2003'), 106))If this is in a stored procedure, then for the sake of clarity I'd do the conversion of the input parameters into local variables. Then use those local variables in the select statement.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply