get date without the time - SQL Server Forums

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)

get date without the time

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 00:17:05

hi, please help me.

ihave this codes:

SELECT Distinct ATB FROM DB
Where (ATB >=('2011-02-01') and ATB <( '2011-03-01') )
order by ATB desc

when i execute it, it displays the date and the time.

2011-02-28 23:59:00.000

how can i edit it so that it only get the date not the time?
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 00:21:48

[code]
select Distinct dateadd(day, datediff(day, 0, ATB), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 00:27:13

i have edited the codes to look like this

select Distinct dateadd(day, datediff(day, 0, ATB), 0) FROM DB Where (ATB >=('2011-02-01') and ATB <( '2011-03-01') )
order by ATB desc

but the error it gave was:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
----
when i remove the order by,and i go to my SSRS to check, there is no fields there.
please help

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 00:30:32

nvm.. about the codes. now i have to edit the codes so that user can specify which date they want. like for example between 2011-02-01 to 2011-03-01. when they specify it in the SSRS, the records between these dates will be shown.

how do i do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 00:33:13

[code]
select Distinct dateadd(day, datediff(day, 0, ATB), 0) as ATB
FROM DB
Where (ATB >=('2011-02-01') and ATB <( '2011-03-01') )
order by ATB desc
[/code]

or

[code]
select Distinct dateadd(day, datediff(day, 0, ATB), 0)
FROM DB
Where (ATB >=('2011-02-01') and ATB <( '2011-03-01') )
order by dateadd(day, datediff(day, 0, ATB), 0) desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 00:37:06

quote:
Originally posted by somenoob

nvm.. about the codes. now i have to edit the codes so that user can specify which date they want. like for example between 2011-02-01 to 2011-03-01. when they specify it in the SSRS, the records between these dates will be shown.

how do i do that?

pass the @start and @end date as a parameter


declare @start datetime,
        @end   datetime
select Distinct dateadd(day, datediff(day, 0, ATB), 0) as ATB
FROM   DB 
Where (ATB >= @start and ATB < @end )
order by ATB desc

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 01:46:25

i have created three dataset. DB, start_date and end_date.

in my DB dataset, part of the code is written like this ATB_DT >= @start and ATB_DT < @end.

in my start_date and end_date dataset, the codes are:

select distinct dateadd(day, datediff(day, 0, ATB_DT), 0) from [staging_db_ORS].[dbo].[V_OPS_VSL_DTL_CT].

when i click preview, there is no date for me to select

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 02:10:37

not very sure what do you mean by that, i guess it is related to the your front end application development environment. Which is the part i have not familiar with. Perhaps somebody else can help you with this if you can provide more information

KH
[spoiler]Time is always against us[/spoiler]

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-27 : 02:17:21

If you wan to get only the Date and not time then I think you can make use of CONVERT with appropriate style to achieve it.

--Replace Getdate() with your date column
SELECT CONVERT(VARCHAR(10), getdate(), 101)
SELECT CONVERT(VARCHAR(10), getdate(), 102)
SELECT CONVERT(VARCHAR(10), getdate(), 103)
SELECT CONVERT(VARCHAR(10), getdate(), 104)
SELECT CONVERT(VARCHAR(10), getdate(), 105)
SELECT CONVERT(VARCHAR(10), getdate(), 106)
SELECT CONVERT(VARCHAR(10), getdate(), 107)
SELECT CONVERT(VARCHAR(10), getdate(), 110)
SELECT CONVERT(VARCHAR(10), getdate(), 111)
SELECT CONVERT(VARCHAR(10), getdate(), 112)

Best Regards
Vadivel

http://vadivel.blogspot.com

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-27 : 02:17:48

its ok. i think i solved it. with the codes:

SELECT DISTINCT CONVERT(VARCHAR(10), ATB, 120) AS ATB from DB ORDER BY ATB ASC

Thanks
-------------

i have another code which requires me to display out the month 2011-01 to 2011-07 in the preview tab of the SSRS. the codes i have is

SELECT DISTINCT LEFT(CONVERT(VARCHAR,ATB,112),6) AS year_mth,
RIGHT(CONVERT(VARCHAR,ATB,106),8) AS year_mth_label
FROM DB
WHERE CONVERT(VARCHAR(10), ATB, 120) IN ('2011-01','2011-02','2011-03','2011-04','2011-05','2011-06','2011-07')
ORDER BY year_mth DESC

but i cant seem to get the date in the preview tab. how should i edit it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 02:38:06

don't apply function on the column. It is bad for performance.


WHERE CONVERT(VARCHAR(10), ATB, 120) IN ('2011-01','2011-02','2011-03','2011-04','2011-05','2011-06','2011-07')

WHERE ATB >= '20110101'
AND   ATB <  '20110801'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 02:41:41