i hav doubt for Compare Two Different Column in Table.. thanks in advancei want 2 disply date part from datatime datatype.. LCID=3081 often solves this kind of problem, but not here.(I can use parameters I believe, but I’m stubborn. Make sure you read this artcle that explains why it happens Vrushali, I hope you have already resolved your problem. Now i can’t change the field type as i contains real data and lots of other application uses this DB. error…thnx madhivanan but in my case this will nt wrk..Like ( Date_From and Date_To) i need to search for Date_From and Date_To when i entered Date_From Text Box and Date_To Textbox .. so wrote a query “select convert(datetime, month_payroll, 102) as month_payroll from monthly_rates_swap” & i get o/p as = “2008-04-29 .937”but i want o/p = ’29/04/2008′can any1 pla tell me tht how to convert datetime into simple date…coz in my company, datetime datatype is used for date… Anyway this is very simple in fact.e.g “SELECT CONVERT(CHAR(11), GETDATE(),0)” will give you a date like ‘May 27 2008’You can experiment with different arguments for interesting results which suits your requirement. i want to design a query which compares dates i.e select * form Emp where joining_date(its in varchar type) between ‘datefrom’ and ‘dateto’how i select date value from sql? i am having some dates like 2012-01-10 .000 2013-01-18 .000 2014-01-22 .000 2015-01-17 .000 2016-01-18 .000 2017-01-29 .000 2018-01-01 .000 2018-05-15 .000 2020-01-20 .000 2021-01-01 .000 2022-05-12 .000 2023-03-25 .000 2024-06-06 .000 if i run this query on these will nt b changed..it ll compare Two Columns and retrive the Date to fill in Grid.. Cheers Hmm I Have A Problem: I Have a Value Like this: Date: ’04/05/2005′The Problem: 1. Is SQL Server give the data to which date it is inserted into database. Thanks in advanceselect count(*) from where convert(modfd_date, varchar(10),110) = select convert(mon_dt, varchar(10), 110) from (select case When datepart(weekday, getdate()) = 2 Then getdate() else dateadd( day, -datepart(weekday, getdate() -2), getdate() ) End as mon_dt) t If I use convert function I get the error “‘varchar’ is not a recognized built-in function name.”What is the best way to select data on a date column without using the time part. as i need to convert these dates as year+01+01 for alll This result fulfill my requirement for difference between start time and end time SELECT DATEDIFF(minute,(SELECT (CONVERT(VARCHAR(10), GETDATE(),111)+’ .000′)),(SELECT (CONVERT(VARCHAR(10), GETDATE(),111)+’ .000′)))Diff between current date time stamp and today other time: SELECT DATEDIFF(minute,current_timestamp,(SELECT (CONVERT(VARCHAR(10), GETDATE(),111)+’ .000′))) AS Diff Date Hello Sir here my problem is i have to get values from a txt file using bulk insert BULK INSERT tbl_Trial FROM ‘C:\Output\inout.txt’ with ( FIELDTERMINATOR=’ ‘, ROWTERMINATOR=’0x0A’)this is the sp am using for that.

azdg dating plz-87

Kindly help Select convert(varchar,’10/09/2007′,1) , Select convert(varchar,’10/09/2007′,101), Select convert(varchar,’10/09/2007′,110)Doc Date & Doc Due Date is getting from temp table SELECT GRVDATE= CONVERT (VARCHAR(15), Doc Date,106) from #Head SELECT REVDATE= CONVERT (VARCHAR(15), Doc Due Date,106) from #Head I need to store in a table having the Doc Date & Doc Due Date datatype as datetime while doing this I got the error “Input string was not in a correct format.” plz help SELECT [NAME], CONVERT(VARCHAR(10),[OUTDATE],101) AS DATECONVERT, [OUTUSER] FROM CASEIO ORDER BY [OUTDATE] DESC;does the job for me, you may want to refer to this MSDN article : convert and string method has more overhead than you think if you need to use it in large rowsets. my current syntax is SELECT * FROM mytablename ORDER BY datefield ASC. my result are : 01/02/2009 03/01/2009 04/06/2009 05/03/2009 06/12/2008 07/02/2009result should be : 06/12/2008 03/01/2009 01/02/2009 07/02/2009can anyone teach me the simple way to solve this problem and give some help please….

Solve this Problem….“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.”As I said in my previous comment, formation is the job of front end application. So my suggestion is to use CONVERT(DATETIME, CONVERT(INT, Get Date())). 1 has taken leave from 5th march to 10 marchso record will be1 5-MAR-2012 10-Mar-2012i want result in vertical like Emp No Date 1 5-MAR 1 6-MAR 1 7-MAR 1 8-MAR 1 9-MAR 1 10-MARThanks Common Sense.

If you still need it, use convert function with style 120. My problem is not reading the date but inserting it into the database in ASP. Another advantage to integer cast versus string cast is that it’s simpler to use in ordering, filtering and so on (think about BETWEEN). Please check the date of the posting the blog post – it was posted in 2007 and we did not have DATE datatype at that time. Kind Regards, You replied me coz it hurts u.didn’t you replied this guy which was in too much trouble , seeee…durga select convert(varchar(4),datediff(dd,getdate(),’01/01/2009′)) giving result as * instead of truncating the value please can you clear me on this? I need to update the part of the date my date is 02/05/1987 i am having 40000 records.. my all dates are random and i want make them look like as 01/month/year…

Also note that hyphenated values is ambigous Refer this post for more informations about datetime formats working in with backend of SQL Server.. egmydate=”3/4/2008″ Insertqry=”Insert into mytable ( [birthday]) ) values ( ‘ & CDATE(mydate) & “‘)results in 4/3/2008 going into the database.session. You should always use unambigous date formats like YYYYMMDD format. I have a vachar field in DB to store dates (which is actually creating problem). means just need to set the day as 01 (update table set day(date.)=01 gives syntax.

Select * FROM tbl Test where Date LIKE ‘%05_2005%’Result: I Have 2 Records (’04/05/2005′, ’12/05/2005′)3. Not sure if SQL Server will use an index if a function is applied to a indexed column. Hi, I am using MSSQL server and I want to retrieve all records from [case] table where date_received is like ’′ and for this I am using below listed query (1) but its not returning me any record since there are matching records present in table.1. Hi sir, when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC Error occur was: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Select * FROM tbl Test where Date LIKE ‘%04_05_2005%’ Or Date LIKE ’04/05/2005′ Or Date LIKE ‘&04/05/2005&’Result : 0 recods : S: S WHere Is The Problem , Can u Help Me ? select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%%’2. In that iam checking existing dates are there or not. Hi sir, when i use this query for sorting date wise order by CONVERT(datetime,[date],103) DESC Error occur was: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. Sir, I am raghu i am having problem with this query Q.

select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%2008%’Query 1 does not return any record but 2 retuns me results. Regards, Lavee Respected Sir, I am developing a Payroll software in VB6.0 and using SQL as backend. I have used below query SELECT COUNT(Leave Application ID) AS Expr1 FROM Leave Application WHERE (CONVERT(CHAR(10), From Date, 101) BETWEEN ’09/09/2008′ AND ’09/13/2008′) AND (CONVERT(CHAR(10), To Date, 101) BETWEEN ’09/09/2008′ AND ’09/13/2008′)Fromdate is 09/12/2008 and todate is 09/18/2008it’s giving count as 0. To String(); //i wanna show here only date bool bt; bool. Display the order ID of the top five orders based on the Total amount due in the year 2001.hint: You can extract the Year part from a date using the Datepart Functioni am not getting the proper outputi am using SSMS 2005, Database Adworks please reply is helpful however, I bumped into problems when I used convert instead of dateadd function to extract the datepart.

@Senthil Select Column_Name From Information_Schema. Columns Where Table_Name = ‘mytable’Replace mytable with he name of actual table in above script. Instead of Getdate() iam specifying directly the date. Also, you still need another CAST to turn it back into datetime. ^_^Try this SELECT * FROM mytablename ORDER BY cast(datefield as datetime) ASCAlso always use proper DATETIME datatype to store dates I guess not so short: The convert function is rounding the value. I create a view that pulls down safety data from corporate site based on local id.

This will give you list of column in vertical structure. An alternate method one is to cast to float, FLOOR, and cast back to datetime. DATEDIFF..) allows determinism, keeps all calculations within the datetime datatype, and guarantees complete locale insensitivity. So dates that has passed noon will become tomorrows dates — correct value would be: Convert(Int, convert(float, Get Utc Date()))Converting from float to int will truncate the value. I then need to derive the number of day’s since last incident based on certain codes and do other calculations. Should I create another view or table or maybe even do sql code in ASP?