Extracting date part in SQL server
-
Dear all; I have a datetime field that stores data like "15/11/2006 10:12:23 AM" how can I extract the date part only without concatenating month and day and year inside the Sql statement as i have LOTs of data to be extracted. I tried to cast to char(10) and then convert to date but it was too slow. Thanx
Scorpion
-
Dear all; I have a datetime field that stores data like "15/11/2006 10:12:23 AM" how can I extract the date part only without concatenating month and day and year inside the Sql statement as i have LOTs of data to be extracted. I tried to cast to char(10) and then convert to date but it was too slow. Thanx
Scorpion
ThaScorpion wrote:
how can I extract the date part only
Use the following in stored procedure, convert(varchar,checkindate,101) as checkindate This will extract only 15/11/2006 only. For ur reference: checkindate --> Field in the table. Hope u will understand it.
Janani
-
ThaScorpion wrote:
how can I extract the date part only
Use the following in stored procedure, convert(varchar,checkindate,101) as checkindate This will extract only 15/11/2006 only. For ur reference: checkindate --> Field in the table. Hope u will understand it.
Janani
I mean extracting as date not just a string ! As converting to varchar ordering as date will fail !! If I will say "Order By convert(varchar,checkindate,101) as checkindate" the sort order will not be correct. Is there anything in SQL server that solves that problem ?? hope u understand it. Thank u
Scorpion
-
I mean extracting as date not just a string ! As converting to varchar ordering as date will fail !! If I will say "Order By convert(varchar,checkindate,101) as checkindate" the sort order will not be correct. Is there anything in SQL server that solves that problem ?? hope u understand it. Thank u
Scorpion
-
use dateadd(day, datediff(day, 0, checkindate), 0) as checkindate to truncate the time value
Thank you indianet for your reply but i think i will face the same problem of speed. i didnt try it yet, but do u think it will not be slow ? There is a function in Access that do the solution in one step like : "Order by DateValue(CheckInDate)" and thats it ! there is nothing like that in Sql Server ?! thanx
Scorpion
-
I mean extracting as date not just a string ! As converting to varchar ordering as date will fail !! If I will say "Order By convert(varchar,checkindate,101) as checkindate" the sort order will not be correct. Is there anything in SQL server that solves that problem ?? hope u understand it. Thank u
Scorpion
select convert(varchar,Reminder_date,101) as Reminder, Reminder_From, Reminder_Until, Reminder_AlarmTime, Create_dt, Update_dt from dbo.TBL_Reminder order by Reminder It was possible to order by date.
Janani
-
select convert(varchar,Reminder_date,101) as Reminder, Reminder_From, Reminder_Until, Reminder_AlarmTime, Create_dt, Update_dt from dbo.TBL_Reminder order by Reminder It was possible to order by date.
Janani
Thanx alot till i Try it :rose:
ThaScorpion
-
Thanx alot till i Try it :rose:
ThaScorpion
Have U order it by date?
Janani