Just a week ago my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with date.
SELECT GETDATE()
ResuleSet:
2007-06-10 7:00:56.107
The required outcome was only 2007/06/10.
I asked him to come up with solution by using date functions. The method he suggested was to use
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I approved his method though, I finally suggested my method using function CONVERT.
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed on MSDN.
Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.
Here is the video discussing the same concepts.
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL
Thnx.
It helped me lots.
Good job! MVP….
proud of you..
dear friend,
i need to display column names only from table
give me you thoughts
@Senthil
Select Column_Name From Information_Schema.Columns
Where Table_Name = ‘mytable’
Replace mytable with he name of actual table in above script.
This will give you list of column in vertical structure.
If you just want to see column names from a table without actually seeing data in the table in horital structure, then use below script
Select * from mytable
Where 1=2
Replace mytable with he name of actual table in above script.
Explanation: Because 1 is never equal to 2, you will always get the column names and will not see data returned by the query.
~ Peace.
Try sp_columns tablename
Type your name in query window select the text and press ALT+F1
how to get the server’s current date, which is connected through linked server in sql server 2005
SELECT GETDATE()
tks!
cheers mate!
Thanx dude!!! Good work:)
What a wonderful explanation…..
You must be a top noch programmer or a system solution developer……
Lots of love and thanks
How should I change the query to update the “LastModifiedDate” column in my table to a specific value?
update your_table
set LastModifiedDate=’date_value’
HI.
I want to display the date in U.S. format.
Instead of Getdate() iam specifying directly the date.
I have tried using these syntaxes.. It is not changing to that format.
Kindly help
Select convert(varchar,’10/09/2007′,1) ,
Select convert(varchar,’10/09/2007′,101),
Select convert(varchar,’10/09/2007′,110)
SELECT CONVERT(VARCHAR(10),GETDATE(),104)
SELECT CONVERT(VARCHAR(10),GETDATE(),105)
SELECT CONVERT(VARCHAR(10),GETDATE(),106)
SELECT CONVERT(VARCHAR(10),GETDATE(),107)
Display is the job of front end application. If you dont use any, as said, use CONVERT function
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 :
http://msdn.microsoft.com/en-us/library/aa237895(v=SQL.80).aspx
thanx ,
it helped me alot
thankyou for this function
The convert and string method has more overhead than you think if you need to use it in large rowsets.
Also, you still need another CAST to turn it back into datetime.
An alternate method one is to cast to float, FLOOR, and cast back to datetime.
Finally: the function usually is non deterministic if you use string to datetime (or vice versa) because the date format is culture and locale sensitive. CAST and CONVERT also mess up determistism.
Using the DATEADD(..DATEDIFF..) allows determinism, keeps all calculations within the datetime datatype, and guarantees complete locale insensitivity.
Determinism is important for indexing computed columns, or if the function is used in WHERE clause
[...] code review of Jr. DBA I found interesting syntax DATEFORMAT. This keywords is very less used as CONVERT and CAST can do much more than this command. It is still interesting to learn about learn about this new [...]
you are too much…i never knew that something like this exist.
i have been having problem exractin date from smalldatetime,and you have solve the problem for me
Nice Solution
It is Vey helpful for My duplicate Transcation entry where date is set as Index.
Thanks
Hello,
I am extracting year from date in oracle i. e. to_char(sysdate,’yyyy’) Now i want to change my database from oracle to SQL Server 2005. So the point is how to convert
to_char(sysdate,’yyyy’) from oracle to SQL Server 2005.
Plz help me out.
Thanking you,
Tushar
select datepart(year,getdate())——
you’ll be getting only year part as ’2010′
or you can use
select year(getdate())
Hai Tushar,
You try this select datepart(yyyy,getdate())
rgds
or
select year(getdate())
Thnax Buddy Nice Examples its working a lot….
i personally feel u should add some more exapmles
for sql queries which screw the programming day to day
any ways thanx
Sarthak…
If you need more example, keep following the blog serious about datetime column
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
Gr8 work dude.. ppl face number of problems working with datetime…
Regards
Bharath T
10x…..
I want to convert varchar type date into datetime so i am getting the following error.
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.”
It means that some of the values can’t be converted to datetime values. Can you post some of the sample data? Also what is the format that date values are stored?
Dear Sir/Madam,
Can we use CONVERT while index a date field?
Usage of Convert function will not make use of index
If you have index and want to remove time part, use method 1
But what if I need the date to be 2007-12-14, with hyphens! SQL Server only offers me 2007-12-14 00:00:00, I don’t need the time at all.
As I said in my previous comment, formation is the job of front end application. If you still need it, use convert function with style 120. Also note that hyphenated values is ambigous
Refer this post for more informations about datetime formats
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
It helped me a lot.
Thanks
nice soln
I want to do date format conversion while retrieving date from database.
is it possible?
thks
Read about Convert function in SQL Server help file
Hey Thanks a lot.Its works.
GOOD
I m lloking for a sql server query which will display the report between two dates from the database where datetime field is defined.
Sachit
where date_col>=@from_date and date_col<=@to_date
I m looking for a sql server query which will display the report between two dates from the database where datetime field is defined.
Sachit
where datetimecol>=’your_date’ and datetimecol<dateadd(day,1,'your_date')
Thanks
It works best for me
Actually from last 2 weeks i m searching this query
and finally i m very happy that i will now process my project
thanxs alot!!!
i’m working in Asp.net with backend of SQL Server.. i hav doubt for Compare Two Different Column in Table..Like ( Date_From and Date_To) i need to search for Date_From and Date_To when i entered Date_From TextBox and Date_To Textbox .. it ll compare Two Columns and retrive the Date to fill in Grid.. still i’m not able to Get the Concept .. how to Find this? if anybody pls rep as soon as possible..
thanks in advance
Apply this logic
where date_col>=@from_date and date_col<=@to_date
Sir,
if db have more than one field having DateTime datatype than how can one specify the particular field in a sql query ?
Where particular_date_col=’some date value’
Thanks! Helped a lot!
Helped a lot!!
my query was as follows (viral this might help you)
SELECT * FROM MRO_MRSA where
AdmissionDate BETWEEN DATEADD(D, 0, DATEDIFF(D, 0, @dateFrom)) AND DATEADD(D, 0, DATEDIFF(D, 0, @dateTo))
Same problem here dude can u provide me solution , if u get actual soln.
Thanks & Warm Regard
Amit Kumar
[removed phone number and email address]
If input dates have time too, you may need to change the logic to
SELECT * FROM MRO_MRSA
where
AdmissionDate >=DATEADD(D, DATEDIFF(D, 0, @dateFrom),0) AND
AdmissionDate <DATEADD(D, DATEDIFF(D, 0, @dateTo)+1,1)
Hii
Ur explanation helped me solve my problm
Thanks a lot
i want 2 disply date part from datatime datatype..
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 13:44:17.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…
n i want 2 dipsly only Date in SQL..not date & time…
i searched a lot on net..but i dnt get any ans…
plz help me.
thx in advance..
use convert function with style 103
Hi Dave
Just very curious how to seperate Time from Datetime?what is the best way to store and retrieve time alone in a database like 03.00 am or 3.00 p.m ?
Best regards
Sanu
I suggest you to use datetime column
When you input time values like ’3:00 AM’, it will get stored as
1900-01-01 03:00:00.000′
You can get time part easily and do all kinds of date related calculations
My problem is not reading the date but inserting it into the database in ASP. eg
mydate=”3/4/2008″
Insertqry=”Insert into mytable ( [birthday]) ) values ( ‘ & CDATE(mydate) & “‘)
results in 4/3/2008 going into the database.
session.LCID=3081 often solves this kind of problem, but not here.
(I can use parameters I believe, but I’m stubborn. There must be a way of doing it how I want !)
It is because you have used ambigous date format. You should always use unambigous date formats like YYYYMMDD format. Make sure you read this artcle that explains why it happens
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
If i convert date using above method, it will convert into varchar data type, but further we can’t operate date function on it… what to do?
You need to again convert back to datetime. That is why you need to use method 1 only
Hi Vrushali,
I hope you have already resolved your problem.
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.
Cheers
SELECT CONVERT(VARCHAR(10),GETDATE(),109)
Aslamo Alikom
I’ve one qeustion that i wnat to use “like” word instead “=” in the query that retrieve the Date…. thanks
Why do you want to use like on dates?
Can you post some more informations?
Cheers
i need date in 05 jun 2008 format can u help me to resolve this
This is the formation. Do it in your front end application
Select Convert(char(11),enddate,13)as date,* from exexamdate
I have date in varchar eg ’20080602-13:47:08.603′
wnat to extract milliseconds only like 603,could you please tell me the way.
1 Always use proper DATETIME datatype to store dates
2 declare @d varchar(30)
set @d=’20080602-13:47:08.603′
select datepart(millisecond,cast(replace(@d,’-',’ ‘) as datetime))
Hmm I Have A Problem:
I Have a Value Like this: Date: ’04/05/2005′
The Problem:
1.
Select * FROM tblTest
where Date LIKE ‘%2005%’
Result: I Have 4 records
2. Select * FROM tblTest
where Date LIKE ‘%05_2005%’
Result: I Have 2 Records (’04/05/2005′, ’12/05/2005′)
3. Select * FROM tblTest
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 ?
The problem is you are converting date values to varchar and compare
What do you want to do?
Hi Pinal,
Your articles are very useful to all.
I am working in database backup utility.
I neet to select only from & to date data.
But tables has no column of date datatype.
Is SQL Server give the data to which date it is inserted into database.
Thanks In Advance
If you dont have a datetime column, it is not possible to know when data are actually added
You rock. Finally a simple way to get all the records for Today. :-D
Your site is now in my favorites…….
hi,
thank u..its help me lot
Hi all,
How do I make this work? Thanks in advance
select 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. (other than the option stated above ie., DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) )
I have an index on the date column and would like to use it. Not sure if SQL Server will use an index if a function is applied to a indexed column.
Thank you for any help.
convert(modfd_date, varchar(10),110)
should be
convert(varchar(10),modfd_date, 110)
Also what did you want to do?
If you give us more infrmations, there is a change that there is a simpler solution
Excellent, elegant, easy. Many thanks.
Hi
If we give Month and Year, Can we get the Start Date and End Date of the Month for that particular year. is it possible?
Regards
Sachin
Yes. Refer this
Hi,
I am using MSSQL server and I want to retrieve all records from [case] table where date_received is like ’12-02-2008′ 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. select (select convert(varchar, date_received, 110) as date from [case]) where date_received like ‘%12-02-2008%’
2. 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.
Can anyone please help me out?
Regards,
Lavee
You should use
select convert(varchar, date_received, 110) as date from [case]
where
date_received >=’20080212′ and
date_received <'20080212'
select convert(varchar, date_received, 110) as date from [case]
where
date_received >=’20080212′ and
date_received <'20080213'
Respected Sir,
I am developing a Payroll software in VB6.0 and using SQL as backend.
I have a problem that i cant subtract the time from the datetime datatype
Please help me in subtracting datetime.
Regards,
Shashi Kant.
Select Convert(Varchar(15),GetDate(),108)
select date_col-’10:00:00.000′ from your_table
Hello Sir,
I need a query to retrive record from the table based on day alone,month alone,year alone.for eg if i run that query in the august i will get the record which and all created in that month.
where month(date_col)=8
hi i ha too mare solutions watch it in different formats
SELECT CONVERT(VARCHAR(10),GETDATE(),100)
SELECT CONVERT(VARCHAR(10),GETDATE(),101)
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
HI,
this is mahalakshmi. I am doing leave application using C#.net. In that iam checking existing dates are there or not. I have used below query
SELECT COUNT(LeaveApplicationID) AS Expr1
FROM LeaveApplication
WHERE (CONVERT(CHAR(10), FromDate, 101) BETWEEN ’09/09/2008′ AND ’09/13/2008′) AND (CONVERT(CHAR(10), ToDate, 101) BETWEEN ’09/09/2008′ AND
’09/13/2008′)
Fromdate is 09/12/2008 and todate is 09/18/2008.
but it’s giving count as 0.
Please solve my problem.
Try this code
SELECT COUNT(LeaveApplicationID) AS Expr1
FROM LeaveApplication
where
FromDate>=’20080909′ and
FromDate=’20080909′ and
toDate<'20080914'
thanks it really worked
hiiiiiii
can anyone help me out with this problem am facing with ie
am not able to use between command in sql server
am getting errors
hi all
i have 6 columns with data in it but after giving this command
(select * from income where dt between ‘sep 7 200′ and ‘sep 9 2008′)
command is executed successfully but the output is
am getting only the names of the columns without data in it
please help me out
CHANGE THE DATE FORMAT AS ‘YYYY-MM-DD’
No. You need to change the date format to YYYYMMDD.
Refer this to know the reason
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
hi
i working on one project ie payrol system in that i hv to calculate employee present days automaticaly including late mark and all
plz help me hw i can calculat time diff
i insert time in database using date time picker
You can use datediff function to find the difference between the two dates
Hi,
How can i use this in a multiple row query
how can i get date in the format “dd/MM/yyyy” with time included init.i have done a lot of googling.but cant able to manage.
tried with
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)——> 28 Apr 2006 12:39:32:429AM
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)—–> 28/04/2006 12:39:32:429AM
but they are returning a garbage values
Use
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
In view we use this contiction,we get record only datawise not time
i had use the following function to get only the date part from datetime data type.
set @data=CONVERT(varchar(8), @feedbackdate, 112)
it did work fine and returned the date part as 20081005
but while again using the procedure to insert the values
i.e.
Insert into tblfeedback (feedbackid,customerid,feedbackdate,feedbacktime,description) values(@maxUserId,@customerid,@data,@feedbacktime,@description)
again it gets changed into the same and displays the time too
please give me a solution for the same thankx
Datetime column is the combination of both date and time
You cant have date only until you use date datatype of SQL Server 2008
i required only time in select query from datetime field in format like:
2:20:55 AM.
if any body know then tell me.
Read about CONVERT function in SQL Sefver help file
You will find the answer
[...] year ago I wrote post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting datepart from [...]
DECLARE @ret_string varchar (255)
DECLARE @year as varchar(10)
DECLARE @Month as varchar(10)
DECLARE @Day as varchar(10)
set @year = cast(year(getdate()) as varchar)
set @Month = cast(month(getdate()) as varchar)
set @Day = cast(Day(getdate()) as varchar)
EXEC xp_sprintf @ret_string OUTPUT, ‘%s-%s-%s’, @year, @Month, @Day
PRINT @ret_string
Result will be like this
2008-10-26
Are you using MySQL or MS SQL Server?
If it is Mysql, post at http://www.mysql.com
Answer to the ” Vrushal” @ 34
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
SELECT CONVERT(VARCHAR(26), GETDATE(), 13)
27 Oct 2008 00:17:58:423
SELECT CONVERT(VARCHAR(26), GETDATE(),22)
10/27/08 12:25:10 AM
Select Convert(char(11),getdate(),8)
SELECT right(GetDate(),7)
Sir,Plz send me the query to retrieve only time from datetime column.
select convert(varchar(10),getdate(),108)
hai
ur Website Very UseFul For Me And Us
JAI HIND
How do I count on just the date portion of a datetime field?
tnx
select dateadd(day,datediff(day,0,getdate()),0),count(*) from table
group by dateadd(day,datediff(day,0,getdate()),0)
select replace(convert(varchar(11), getdate(), 106), ‘ ‘, ‘/’) as date
result
———
date
12/Dec/2008
Testing for December 31st to get resultset of data for anything greater or equal to the 31st, but keep getting all of 2008 data.
Using:
select * from TABLE where enddate >= CONVERT(VARCHAR(10),’2008-12-31′,111)
Have you tried this?
select * from TABLE where enddate >= ’2008-12-31′
Answer for @Athar, @Preeti
Query to retrieve only time from datetime column.
select convert(varchar(10), getdate(),108) –without millisecond
OR
select convert(varchar(10), getdate(),114) –with millisecond
Hi,
I want to retrieve only the date part from datetime from the SQL database. I am using
SELECT CONVERT(VARCHAR(10),GETDATE(),111) as sowDate
But it is giving me the current date :(
Plz help me.
You need to use the actual column name
SELECT CONVERT(VARCHAR(10),date_col,111) as sowDate from your_table
I have a problem with dates that are entered in MS Access but are out-of-range like 01/02/830. Can u please help me with a query that can retrieve such dates for correction before I can import the data to SQL Server 2008.
Use isdate() function
where isdate(date_col)=0 or len(date_col)<10
Hi,
Thanks for your blog..
I have a problem in comparing 2 date fields one has the time field as (2009-01-16 12:32:50.690) and the other (2008-08-07 00:00:00.000).
I want to compare the date part of the 2 fields. I tried coverting them to varchar (101) and using the <= operator but the result set was incorrect.
I am not sure how else I can achieve this functionality of comparing the date part only.
Use this approach
dateadd(day,datediff(day,0,date1),0)=dateadd(day,datediff(day,0,date2),0)
thanx a lot….
@Ankit,
You need to use styles with convert.
Format of convert is
convert ( New Data Type, Column_Name or variable, Style)
select A.* , B.* from table_name A , table_name B
where convert ( varchar(10), A.Date_column_name , 101) = convert ( varchar(10), B.Date_column_name , 101)
Hope this helps,
IM
It is better to compare dates as dates rather than varchar to varchar. See my previous reply
Thank you sir
This topic helped me a lot but can u tell me what is the relation of sequence of the number with date format
It is used to format the datetime values in specific reginal format
[...] SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice [...]
Hello Sir,
I have one date problem regarding importing XML Date to sql Server 2005.
I am passing DataTable to SP from .NET Application C#..
the columns with DateTime data type having
Default value as :
col.DefaultValue = DateTime.Today.Date;
and
string strXML = ds.GetXml();
and passing this strXML to SP.
My XML have tag like :
2009-02-28T00:00:00+05:30
SP is not accepting the date I pass above to datetime field of SQL server 2005.
can you plz. suggest wt could i do for this..
I tried for
select cast(2009-02-28T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,
‘DATETIME’)
but it is giving NULL Value.
Thanks.
Reagerd
Either you need to remove +5:30 from your_date or convert it to actual date by removing +5:30 and adding 5:30 hours
Hello Sir..
My above reading DataTime from XML get solve using above mentioned casting.
Due to my some mistake in Query I was getting NULL value.
Well, but now I am fasing new problem for this DateTime only of XML…
I have two tags in my XML datatype string which are
2009-01-01T00:00:00+05:30
2009-12-31T00:00:00+05:30
Actualy these dates are 01/01/2009 and 31/12/2009 respectively
But In OpenXML when I convert these dates it gives me different OUTPUT
(Here with I am showing you in select )
select
cast(’2009-01-01T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,'DATETIME’) as StartDate,
cast(’2009-12-31T00:00:00+05:30′ as xml).value(‘xs:dateTime(.)’,'DATETIME’) as EndDate
OUTPUT is :
2008-12-31 18:30:00.000 2009-12-30 18:30:00.000
respectively ..
Can you please suggest Why this is..?
Regards
Namasthe Pinal
Could you tell me why my DB return is different?
select getdate()
3/3/2009 3:11:56 PM
I wish it was
3/3/2009 15:11:36
Why does formation matter at query analyser?
When you use front end application, do formation there
Also make sure to read this post to understand datetime column
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
Date representation in the DB and Date formatting are two different things.
You take too simplistic approach.
For example, in a native date field, I can perform date arithmetic, but not in a varchar, or string field.
Hence please dont treat them as one and the same.
Oracle has a native date field – 01-01-2009 01:12:35
You can apply a “trunc” function to chop off all time, i.e.
you will get a native ’01-01-2009′.
On both the above values, you can do arithmetic, i.e. add hours, days, or any time interval – you will get a new date as a result.
Both the above dates, trunc’ed one, and the full date,
can be formatted into a varchar or string.
Hence date presentation is different from date.
This is such a common trap programmers fall into.
Dear All,
I been trying to find an optimize way to fetch the data on datetime columns, and preferably i would go for dateadd method i.e. DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())), the reason why i would choose this method over convert is because of my size of table which is almost like 2 millions rows and somehow my datetime column is a part of primary key. Also to note is, if my concern is only to get the date part then its better to use above mentioned method than using convert, as convert will change the datatype of column on the fly to string and its an overhead for the engine to do this step. Surely Pinal might have a better solution but i been using the dateadd for quite a while now and its going smooth.
Happy coding.
Regards- Rahman
@Rahman
If the date part gets queried a lot, it may be worthwhile to have a computed COLUMN in the TABLE that applies the FUNCTION for you. Then, INDEX that COLUMN.
hello sir,
column of a table contains data as
jan09
jul08
aug08
………… like this
how can i select only last two digits of that column
select right(column_name,2) from table_name;
is not retrieving any data
pls help me
What did you mean by “not retrieving any data”?
Post some sample data with expected result
hi siva
use select right(rtrim(column_name),2) from table_name
thanks a lot :)
hello,
i hav used datetimepicker control in my form.it stores date and time both in sql server database.
is there any way to only store time, not date in sql server database?also same for retrieval??
Thank You.
hello,
i hav used datetimepicker control in my form.it stores date and time both in sql server database.
is there any way to only store time, not date in sql server database?also same for retrieval??
Thank You in advance
@Alpesh,
You can use convert styles, to get different formats for date.
Please see a complete list of format styles here
http://msdn.microsoft.com/en-us/library/ms187928.aspx
I have used only 3 formats (108, 109, 114) just to show example:
– only Time ( 24 hours clock)
select convert ( varchar , getdate() , 108)
Output :
23:56:37
GO
– only Time with Micro seconds ( 24 hours clock)
select convert ( varchar, getdate(), 114)
Output :
23:56:48:810
GO
– only Time with Micro seconds ( 12 hours clock)
select substring ( convert ( varchar, getdate(), 109), 13, len ( convert ( varchar, getdate(), 109)) – 12)
Output:
11:57:19:840PM
~ IM.
@Imran
sorry tosay but I asked how to store only time in sql server database . I know how to extract only time/date from tht control…..Plz…How To STORE???
If you use version 2008, you can make use of time datatype
Otherwise you can use either datetime or varchar column
Hi All,
I have a table with two datetime fields, now I want to display both columns as date only.
tablename: sometable with two fields as
created | lastlogin
—————————————————————
2009-06-02 22:02:05.033 | 2009-06-02 22:03:34.503
2009-06-02 22:02:27.530 | 2009-06-02 22:03:50.047
now I want output as
created | lastlogin
————————————-
2009-06-02 | 2009-06-02
2009-06-02 | 2009-06-02
can anyone explain please, Thanks in advance
Sathish.
I have been through all the replies and got it..
SELECT CONVERT(VARCHAR(11),created,102) as created_date, CONVERT(VARCHAR(11),lastlogin,102) as last_date from sometable
Thanks,
Sathish
@Alpesh
declare the the field whatever you want to insert only time as varchar then
while inserting use this function on that field
convert ( varchar(20) , getdate() , 108)
ex:
here
uname as varchar(20)
lastlogin as datetime
created as datetime
query:
insert into table_name (uname, lastlogin, created) values (convert ( varchar(20) , getdate() , 108), getdate(), getdate())
if yours is different case i’m sorry.
Thanks,
Sathish.
I found the soultion. Thanks
I developed the solution for date only from ‘getdate()’ function. Also this worked for me very effectivelyy. Because I does not required to change the data type in the database or not required to write down different function for each time or not required to execute the same function for so many times.
I use this method.
1) Keep data type of column as ‘datetime’.
2)Keep its default value to ‘getdate()’.
3) Write trigger on that table as
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TABLE NAME]
FOR INSERT, UPDATE, DELETE
AS
UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)
It works very effectively.
It is better you make use of a computed column than a trigger
Answer to Seema :
To extract milliseconds from current date
SELECT(DATEpART(ms,getdate()))
We can also select date using
SELECT LEFT(GETDATE(),11)
This is not the reliable method. Depends on the language settings of the server you will get different result
FWIW…
Many people prefer the DATEADD method rather than the CONVERT method because they are trying to compare two dates rather than output a date…
If you use the CONVER method, once you get the string with just the date in it, you have to convert it back to a datetime to do any comparisons with it other than =, and two converts is more expensive than a couple of system function calls in a non-negligible way…
Your team member’s solution was better.
I have a problem of trying to find dates in my table which does not follow the following formats. Given are the valid formats:
1. 10/1/2007
2. Thu Sep 20 06:32:10 GMT-0400 (EDT) 2007
3. 10/1/2007 5:00
Can anybody please help me regarding this?
Thanks in advance.
Use regular expression
where
date_col like ‘[0-2][0-9]/[01][0-9]/[0-9][0-9][0-9]‘
or
date_col like ‘[0-2][0-9]/[01][0-9]/[0-9][0-9][0-9] [0-24]:00′
It was a great help! Thanks!
Thank you so much, but is it possible to display just time?
If you use front end application, show time part there. Otherwise
select convert(varchar(10),getdate(),108)
please help me i want to get data between two dates im writing
select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
can not get the correct data
The proper method is
select * from Test_Date
where
t_Date>’20090202′ and
t_Date<'20100203'
The proper method is
select * from Test_Date
where
t_Date>=’20090202′ and
t_Date<'20100203'
please help me i want to get data between two dates im writing ,these query act normaly but not correct data
select * from Test_Date where t_Date>=convert(datetime,’02/02/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
can not get the correct data
and the datatype of column t_Date is varchar(50)
Three things
1 Always use proper DATETIME datatype to store dates
2 Work on DATETIME and not VARCHARs
3 You should always use unambiguious format YYYYMMDD HH:MM:SS
So your query should be
select * from Test_Date where convert(datetime,t_Date,103)>=’20090202′ and convert(datetime,t_Date,103)<'20090203'
Hi Rabin,
select *
from Test_Date
where CAST(t_Date AS DATETIME)>=convert(datetime,’02/02/2009′,103) and CAST(t_Date AS DATETIME)<=convert(datetime,'02/02/2010',103)
let me know if it helps you.
Tejas
I have a datetime column in database in ’9/29/2009 1:00:10 AM’ this format. Based on this coulmn value i need to get other column value but datetime value would be in ’9/28/2009′ format.
How I can achive following query
select * from tblFirst where Startdate=’9/29/2009′
but in Database Startdate column has value as ’9/29/2009 1:00:10 AM’
Could you help me?
Thanks in advance
Try this
select * from tblFirst
where
Startdate>=’20090929′ and
Startdate<’20090930′
Hi Mrs Tejas Shah thank for you
ok your code is correct but actuall not get
the specific result because if we put from date
’01/03/2009′ to date ’01/01/2010′ we cannot get the
record has date ’02/03/2009′ why ?
Notes: i’m save date in database as dd/mm/yyyy
date column in database has datatype varchar can you help in this code or another way to get the good result.
Thank again for you
You should always use the format YYYYMMDD which is unambigous
@Rabih,
Check if this could help.
Create Table #Example ( Eid int, EmpName varchar(50), DateJoined varchar(50))
Insert into #Example values ( 1, ‘Bill Gates’ , ’01/01/2009′)
Insert into #Example values ( 2, ‘Gustavo’ , ’01/02/2009′)
Insert into #Example values ( 3, ‘Catherine’ , ’01/03/2009′)
Insert into #Example values ( 4, ‘Kim’ , ’01/04/2009′)
Insert into #Example values ( 5, ‘Humberto’ , ’01/06/2009′)
Insert into #Example values ( 6, ‘Pilar’ , ’01/08/2009′)
Insert into #Example values ( 7, ‘Frances’ , ’01/10/2009′)
Insert into #Example values ( 8, ‘Margaret’ , ’01/12/2009′)
Insert into #Example values ( 9, ‘Carla’ , ’01/01/2010′)
Insert into #Example values ( 10, ‘Jay’ , ’01/02/2010′)
Insert into #Example values ( 11, ‘Ronald’ , ’01/03/2010′)
select * from #Example
select *
from #Example
where Convert (Datetime, DateJoined) between ’01/03/2009′ and ’01/01/2010′
Drop table #Example
~ IM.
@W.Ah
Check if this could help.
Create Table #Example ( Eid Int, Ename varchar(40), JoinDate datetime )
insert into #Example Values ( 1, ‘Imran’ , ’9/29/2009 1:00:10 AM’)
Select * from #Example
Select Eid, Ename from #Example
Where convert (datetime, convert ( varchar, JoinDate, 101 )) = ’9/29/2009′
Drop table #Example
~ IM.
Nice Articles,thanx for sharing ur knowledge.
Hi Mr Imran Mohammed
thank for this code but not actuall get the result please i need all records between two dates your code not gell all records.
In my table
date column in my table is varchar and i’m put date in my application like dd/mm/yyyy and i’m writing this code
select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
not get the record has date '02/03/2009′ try this code and see the result please help
thanks again
It should be
select * from Test_Date
where
t_Date>=’20090103′ and
t_Date<'20100203'
Hello Rabih,
r u getting follwoing error :
“The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
while using ”
select * from Test_Date where t_Date>=convert(datetime,’01/03/2009′,103) and t_Date<=convert(datetime,'02/02/2010',103)
statement ??
Adi
select * from #TempTable Where convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) >=convert(numeric,Convert(varchar,convert(datetime,’01/03/2009′,103),112)) and
convert(numeric,Convert(varchar,cast((left(right(t_Date,7),2) +’/’ + left(t_Date,2) + ‘/’ + right(t_Date,4)) as datetime),112)) = and <= condition
Hope it will give u all the results u r expecting.
Do reply.
thanks.
Regards
Adi
Rabih
it seems my select statement is not comming compleately
I did following steps.
Flow is like this :
1) arrange ur string dd/MM/yyyy to MM/dd/yyyy
2) then convert it to Datatime
3) then convert that datetime to numeric format using 112
4) Now convert ur From date and ToDate of conditon to datetime 103
5) then convert those From date and toDate to numeric format
6) and then compare using >= and <= condition
The formats you specified are ambigous
Make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hir Mrs Adi
I’m very very very thank for you
you give me the good solution good work for you.
Bye
if you want send to me your Email Address.
Bye again
Your posts always helps me lot …thanx
Can Datepart be used as part of update…set query. I am trying to update a datefield by the following statement
Update tablexx
Set Datepart(yyyy, Datefld) = 2011.
But I am getting errors.
Thanks,
Ash
If you want to update all years to 2011, use
Thanks alot sir, It works it solved lots of my problems
@Ash
The date still need to be a date.
Do you want to change the fields to be January 1 2011?
SET DATEADD(yyyy, 111, 0).
0 is January 1 1900.
Excellent!!!!!!!!!!!!!!!!
Tanq very much….!!!!!
Exact result for what i searching
Hi All,
Kindly look at this statement:
strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT MAX(logDateTime) FROM LogTbl);”
The problem here is that the actual value which is stored in the table looks like this: 12/2/2009 4:06:55 AM
Now the fetched record will be the latest one. But I want to fetch all the records of that date that is: mm/dd/yyyy.
So, how can we do that in the above mentioned Query Statement? Can anybody help me with it, please? Otherwise, I will have to store the time in a seperate filed in the table which is not that much efficient and worthwhile.
Using your example I tried this but it doesn’t work:
strSQL = “SELECT * FROM LogTbl WHERE logDateTime=(SELECT logDateTime FROM LogTbl CONVERT(VARCHAR(10),logDateTime,111));”
This is the error:
Technical Information (for support personnel)
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error. in query expression ‘logDateTime=(SELECT logDateTime FROM shiftLogTbl CONVERT(VARCHAR(10),logDateTime,111))’.
hi Dev
Use this, it will solve your prblm. I think..
SELECT * FROM LogTbl WHERE CONVERT(VARCHAR(10), logDateTime, 110)=(SELECT MAX(CONVERT(VARCHAR(10), logDateTime, 110)) FROM LogTbl)
Regards
PRAVEEN MUDGIL
Try this
SELECT * FROM LogTbl
WHERE logDateTime>=(SELECT MAX(dateadd(day,datediff(day,logDateTime,0),0)) FROM LogTbl)
Thanks Mr.Pinal. This is Praveen from Xtend Technologies, cochin. I was looking for a solution to get the distinct date part of a date time field in SQL server.
Hello Praveen,
If you are using SQL Server 2008 then you can get distinct date by using below statement:
SELECT DISTINCT CONVERT(DATE,(datetime_column)) FROM tableName
Otherwise use below:
SELECT DISTINCT CONVERT(VARCHAR(10),(datetime_column)) FROM tableName
Regards,
Pinal Dave
Thanks! Short and sweet and it works!
I have a date column, like ‘yyyy-mm-dd’, so i want to retrieve the data from table with only a perticular year..
Please help me..
Thanx..
select * from where datepart(yyyy,) = ’2009′
select * from Table where datepart(yyyy,DateColumn) = ’2009′. Format i was trying to send is not displaying correctly. plz check this if it could help you.
Use the following criteria in WHERE clause:
DATEPART(yy, date_column) = perticular_year
If you want to mkae use of the index of date_column,
where
date_column>=dateadd(year,@year-1900,0) and
date_column<dateadd(year,@year-1900+1,0)
Hope this might help u..
select * from where datepart(yyyy,) = ’2009′
Thanks for this post. Saved me a lot of time.
i need help in visual basic and sql. when i click date in vb components of calendar1 in selected date tat should be access and show through the msflexgrid or another form.
Please help me soooooon!
where
date_column>=dateadd(day,datediff(day,0,selected_date),0) and
date_column<dateadd(day,datediff(day,0,selected_date)+1,0)
I NEED TO KNOW THAT,THERE IS A FIELD CALL DATE,AND ANOTHER HAVE VALUE
DATE VALUE
DATE1 100
DATE2 200
DATE3 300
DATE1 200
DATE3 100
I WANT TO GET RESULT LIKE THIS
DATE1 TOTAL VALUE= 300
DATE 2 TOTAL VALUE=200
……….. .
HOW CAN I DO THIS IN PHP CORD
@Upekha
This is a standard GROUP BY query.
SELECT date, SUM(Value) FROM table GROUP BY date;
Hello,
I am doing a project in access 2007.
I have a table with two fields: startdate and enddate.
i wanted to have an sql statement that will return all the dates within startdate and enddate.
Is this possible?
Thank you very much.
hi hurb,
maybe the below soln will help you,
create table #tempdate1(date datetime)
declare @date1 datetime
set @date1 = ’2010-02-10′
while (@date1 <= '2010-02-15'))
begin
insert into #tempdate1 select @date1
set @date1 = dateadd(dd,1, @date1)
end
select * from #tempdate1
drop table #tempdate1
you will get the dates from 2010-02-10 to 2010-02-15 (based on the condition in the while, where changing the condition in the while you can get different results based on the condition).
you can have this one in a SP and have two input parameters such as startdate and enddate and pass the values to these variables and you can achieve your required soln by minimal changes in the above code.
Hello Hurb,
Union two statements, one for each column as below:
select startdate from table
union
select enddate from table
Let us know if this doesn’t fulfill your requirement.
Regards,
Pinal Dave
Great tip! I went with the DateAdd()
Thanks a Lot !!!!! Pinal
Thanks
Hello sir,
I want to set only the date in 2 variables and compare them.
I used the following code
declare @ydate as datetime,@ctdate as datetime,@dtdiff as datetime
set @ydate=current_timestamp-1
set @ctdate=current_timestamp
select @ydate
select @ctdate
set @dtdiff=datediff(dd,@ydate,@ctdate)
select @dtdiff
and got the output as
@ydate:
2010-03-15 10:07:59.107
@ctdate:
2010-03-16 10:07:59.107
@dtdiff:
1900-01-02 00:00:00.000
I don’t want this output . I need compare the values and find whether the @ctdate is greater than @ydate.
Please help me out.
Hello Koushuikha,
Change the datatype of @dtDiff variable to int.Then use the IF clause to indentify the higher one.
Regards,
Pinal Dave
Thanks pinaldave. I really help me.
Thanks pinaldave. I really help me…
If I need to get a rolling weekly average (week starting on Monday) how would i write that?
select
COUNT(*) as numOfFiles,
NumberOfLoans as numRecords,
convert(char(10),dateadd(week,-1,dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm])),101) as WeekStartDate,
convert(char(10),dateadd(day,-1*(datepart(weekday,[CreateDtTm])-1),[CreateDtTm]),101) as WeekEndDate
from testtable
I can only get my start and end of the week, how would I go about calculating the daily/weekly/monthly averages? Any help in the right direction is greatly apprecaited…
thanks
Hi,
i want to know how i can creat field in table wich it’s data type is date (tt/ee/wwww)
Can you be more specific?
What do tt, ee and wwww mean?
I need a lill help from you all smartys!
I am working on an update of a datetime field and receiving the following error.
Query:
Update tbl
SET Month(column 1) = Month(Column 2),
Day(column 1) = Day(column 2)
WHERE Month(column 1) = Day(column 2)
and Day(column 1) = Month(column 2)
and id in (111,222,333)
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘)’.
Please assist.
Thanks, — imransi —
The above statement is correct. Are you sure this is the code that you executed?
Hi sir,
Is there any way to ensure that the date column is in given format
1 Always use proper DATETIME datatype store dates
2 It is the job of front end application to do formation
of Course this was a good article, but still didn’t get my solution.
i have a date column on my table and i need to assure that all these date are in same formate… just verify, make sure.
ex. all are in MM-DD-YYYY just match formate
As long as you express dates in unambigous formats like YYYYMMDD format, you dont need to worry about how dates are stored in datetime column
my data is correct and isdate returns 0(zero), still i need to check they are in all correct format i.e for my system 2010-7-6 is right where as 2010/7/6 is wrong format.
Make use of set dateformat
set dateformat ymd
–your query
hello,
my
query is
select start_date from employee where emp_Id=”101″;
how to get date only part in database to my textbox display
If you use front end application, use format function there
ex
in VB6,
format(date,”DD/MM/YYYY”)
Hi ,
pls help
how to select the data between May-2000 to March-2010
i tried below query but not working,
select * from tbl_candidate where
(Datename(Month,TestDate) + ” + Datename(Year,TestDate)) between ‘May-2000′
and ‘March-2010′
pls help..
Try this code
select * from tbl_candidate
where
TestDate>=cast(replace(‘May-2000′,’-',’ ‘) as datetime) and
TestDate<dateadd(month,1,cast(replace('March-2010','-',' ') as datetime))
Thanks a lot madhivanan,its working fine
Even better way:
CONVERT(DATETIME,Convert(VARCHAR(8),DATE,112), 112)
Actually the better way is
dateadd(day,datediff(day,0,date),0)
Hi…..
This is very useful to all who are all work in IT field….
Great Article….!!!!!!!!!
Hi,
I want to add Min column in Stored procedure .
ex – Mins(column)
3:10
4:20
5:10
2:30
Output should be 15:10
select sum(col1), min(col2) from table
Hello,
This might be a bit off topic but here goes:
I have the following query which is extracting and counting the day/s of the month from my table whenever a transaction takes place – the column is called incidentdate.
This is working fine but I also need to include in the result of the query days of the month where a transaction did NOT take place, those days should show as zero when counted.. following is my query and the results with the missing days:
‘SELECT day(incidentdate) as DayOfMonth, count(day(incidentdate)) as IncidentCount FROM phl_csapp GROUP BY day(incidentdate);’
DaysOfMonth IncidentCount
2 6
3 6
4 4
5 1
” ”
Problem – the 1st day of the month is missing in the result of the query because I have no transaction (incident) for that day. How do I include the days that I don’t have a transaction to display a zero instead of simply not showing when the query is run……….
I’ve been banging away at this for 4 days now :)
ramdas
Maybe I am not reading it correctly or understanding the date set. In your dataset, is there a column for incident?
I am picturing a column called incidentdate and each time an incident occurs, you are adding the date of occurrence. If this is the only way you are logging it, the best approach would probably be on the front end.
If you absolutely need it in SQL, you will need to loop through the days in the month and select the count for each one. Doing this will create a separate select for each day so, if you want it all in one result table, a temp table storing the count per day would probably be best. Something like:
declare @day int
declare @days int
declare @temp table( DayOfMonth int, IncidentCount int)
select @days = DAY(dateadd(mm, datediff(mm, -1, GETDATE()), -1))
set @day = 1
while @day <= @days
begin
insert into @temp select @day, count(DAY(incidentdate)) from phl_csapp
where DAY(incidentdate) = @day
set @day = @day + 1
end
select * from @temp
This is taking the days of the current month and you could always just make it loop 1 – 31 and eliminate the days piece.
Just my .02.
Incidentally, I did some testing with the convert vs dateadd method and I will back that, though the convert method is comparable performance wise with a small amount of data, it does not scale well.
I am pulling reporting for thousands of data rows and the convert method will increase time to a second or two while the dateadd method will return under a second. When building out a report via a web interface, 1-2 seconds is very impacting when it’s in the back end and you still have a web front end to wait on.
I would say the convert method is great for ease of use on small data sets, but stick to the dateadd method if you have a larger dataset you need to perform the query on.
Yes. It is because the convert does convertion to another datatype whereas dateadd and datediff wont
Stored Procedures: SQL Server 2008:: Visual Studio 2010
Do variables pass from a VB.NET page to a Stored Procedure when you are using a master page?
I have copied the code back and forwards and used the same code / Stored Procedure programme.
I have a table named Orders and the o nly way I seem to be able to select orders by OrderDate is by using a SP. I use a Calendar to collect the FromDate and another to collect the ToDate and put this into a textbox (tbFromDate, tbToDate) (I can’t collect directly from a Calendar nor a label, just a textbox. I have a button to run the Stored Procedure and put the output into a datagrid (dgA)
——————————
Protected Sub ButtLoadData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtLoadData.Click
If IsPostBack Then
Dim DBConn As SqlConnection
DBConn = New SqlConnection(“Server=localhost;Initial Catalog=OLB;” _
& “Integrated Security=SSPI”)
Dim cmd As SqlCommand = New SqlCommand(“Orders1″, DBConn) ‘Do not need EXEC
cmd.CommandType = CommandType.StoredProcedure ‘ New line added
Dim pFromDate As SqlParameter = New SqlParameter(“@FromDate”, SqlDbType.Date, 10)
pFromDate.Value = Request(“tbFromDate”)
cmd.Parameters.Add(pFromDate)
Dim pToDate As SqlParameter = New SqlParameter(“@ToDate”, SqlDbType.Date, 10)
pToDate.Value = Request(“tbToDate”)
cmd.Parameters.Add(pToDate)
DBConn.Open()
cmd.ExecuteNonQuery()
Dim rwReader As SqlDataReader = cmd.ExecuteReader
dgA.DataSource = rwReader
dgA.DataBind()
DBConn.Close()
rwReader.Close()
End If
End Sub
———————————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Caz
– Create date: 22/09/2010
– Description: Orders between dates
– =============================================
CREATE PROCEDURE Orders1
– Add the parameters for the stored procedure here
@FromDate date,
@ToDate date
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT
SuppName,
Orderid,
OrderDate,
CP,
VAT,
Invoice
from Orders
where
OrderDate >= @FromDate
and OrderDate <= @ToDate
order by OrderDate
END
GO
——————————
Am I missing something here or just being stupid?
Any help gratefully received.
Thanks
John
I haven’t encountered any errors when performing similar tasks from either masterpage or template. Are you getting the actual param value? I created a similar approach using my DB and SP on my VB.Net page but I am passing the value through directly. I may be a bit rusty but shouldn’t you specify whether the request object is from a QueryString or Form?
Anyhow, my test on my site was as such. Hopefully this helps somehow. It gave me something to do while I worked out my own SP issue. ^_^
Also, Request.Form could easily be changed to Request.QueryString(“fieldName”) if it’s value through QS or, if an ASP server control TextBox with id “toDate” you could do toDate.Text. Anyways, this is what worked for me.
——————
Dim dbcon As New SqlConnection(“Server=localhost;database=siteDb;Integrated Security=True;”)
Dim dbcmd As New SqlCommand(“getUsers”, dbcon)
dbcmd.CommandType = CommandType.StoredProcedure
Dim ut As Integer = 0, gi As Integer = 0
If (Not Request.Form(“uType”) Is Nothing) Then
Integer.TryParse(Request.Form(“uType”), ut)
End If
If (Not Request.Form(“gId”) Is Nothing) Then
Integer.TryParse(Request.Form(“gId”), gi)
End If
dbcmd.Parameters.Add(New SqlParameter(“@userType”, ut))
dbcmd.Parameters.Add(New SqlParameter(“@groupId”, gi))
Try
dbcon.Open()
Dim dGrid As New DataGrid()
dGrid.DataSource = dbcmd.ExecuteReader()
dGrid.DataBind()
Me.Page.Controls.Add(dGrid)
Catch ex As Exception
Response.Write(ex.Message)
Finally
dbcmd.Dispose()
dbcon.Close()
End Try
Thank you for your help. However I am still having issues.
I am using this to transfer teh data: I know you say something else above but I am not that clever at translating it:
—————
Dim pOrderID As SqlParameter = New SqlParameter(“@OrderID”, SqlDbType.NVarChar, 12)
pOrderID.Value = Request(“tbOrderID”)
cmd1.Parameters.Add(pOrderID)
—————-
I am getting the value from a select column in another datagrid (dgA)
—————-
Protected Sub dgA_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgA.SelectedIndexChanged
tbOrderID.Text = “40″ ‘dgA.SelectedItem.Cells(1).Text
LoaddgB()
End Sub
——————-
I have changed the variable to an actual “40″ and there is a record with OrderID = 40 as an Int in the db.
I get the response
Procedure or function ‘OrdersNonStock4′ expects parameter ‘@OrderID’, which was not supplied.
————————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Caz
– Create date: 23/09/2010
– Description: Order Totals for Non Stock
– =============================================
CREATE PROCEDURE OrdersNonStock4
– Add the parameters for the stored procedure here
@OrderID nvarchar
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT
SUM(CP) as SumCP,
SUM(Delivery) as SumDelivery,
SUM(VAT) as SumVAT,
SUM(Duty) as SumDuty,
SUM(Invoice) as SumInvoices
from Orders
Where OrderID = @OrderID
END
GO
——————–
I’m sorry to be a nuisance but all I am trying to do is select a number of orders held in one datagrid, select one and have the total of the items displayed in another grid. I can then take those totals and update the record with the latest totals.
I guess I ned to go back to school but at my age I’m too old! :-)
Thank you
John
Hi ,
I need to resolve a query wherein I have to display the names of customers who have the same date of birth
CLAUSE: be sure they are not matched against their own record
and the date of birth field is in the table called customer
select columns from customer where dateofbirth in
(select top 1 dateofbirth from customer group by dateofbirth order by count(*) desc)
Hi ,
thanks for the prompt reply , but i din understand the “top 1″ word mentioned in here and it gave the following error when i tried to run the same
select cust_first_name, date_of_birth from customers where date_of_birth in(select top1 date_of_birth from
customers group by date_of_birth order by count(*)desc );
Error starting at line 1 in command:
select cust_first_name, date_of_birth from customers where date_of_birth in(select top1 date_of_birth from
customers group by date_of_birth order by count(*)desc )
Error at Command Line:2 Column:33
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 – “missing right parenthesis”
*Cause:
*Action:
Mote that this site is for SQL Server, For Oracle questions, try at Oracle forums
Hi,
This is very helpful and I will use one of the date methods, thank you. However if you are adding Order by Date which comes after the Select statement then the Convert wouldn’t work as a Date Field. Up to now I have been removing the minutes in the web front end by adding {0:d} which works well and allows the stored procedure to sort happily.
John
Uou need to use a different alias name for date column in the select statement and use original column name in the order by clause
hey,
what about just using
SELECT CAST(GETDATE() AS DATE)
Provided the server is 2008 or above version
This Site Rocks!
I want to display Date without Time on SSRS in a column, how I can do This
Manjeet
Reply me soon??????????
Use format option there
HOw I can Display Date without Time on SSRS
please tell me
i have problem to alter the database field from datetime to integer with values
alter table overtime alter column adate numeric(5)
while using this query am getting error like this
“Disallowed implicit conversion from data type datetime to data type numeric, table ‘DaiichiPayroll.dbo.Overtime’, column ‘adate’. Use the CONVERT function to run this query.
”
any solutions
You need to update that column to NULL before applying the alter statement
i have 2 date in mm/dd/yyyy formate
i want to search data b/w dates
what is easy way.
1 You should use proper DATETIME datatype to store data
2 Make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hi pinal,
Convert function doesn’t work when we apply airthmatic operators with it for e.g
CONVERT(VARCHAR(10),GETDATE(),111)-CONVERT(VARCHAR(10),date,111)
You cannot subtract characters. What do you want to do?
Hi:
Can anyone please help me how to convert string ‘yyyy/mm’ as datetime yyyy/mm/dd in sql 2005 ?
Thanks in advance
select cast(’2010/12′+’/01′ as datetime)
Hi,
what do you think about
cast
(
dateadd
(
day
,
-10,
getdate())
as
date)
Sorry!
cast ( dateadd ( day , -10, getdate()) as date)
Actually I meant:
cast(getdate() as date)
Please delete the previous two
can anybody tell me how to display only date part from database…i’ve taken datetime filed in database…
my corrent coding is
snet.XCmd x = new snet.XCmd(“select * from Personal where email=@p1″);
x.Cmd.Parameters.AddWithValue(“@p1″, Session["email"].ToString());
DataTable dt = x.GetTable();
txtFname.Text = dt.Rows[0][2].ToString();
txtMname.Text = dt.Rows[0][3].ToString();
txtLname.Text = dt.Rows[0][4].ToString();
txtGender.SelectedValue = dt.Rows[0][5].ToString();
txtDob.Text = dt.Rows[0][6].ToString(); //i wanna show here only date
bool bt;
bool.TryParse(dt.Rows[0][7].ToString(), out bt);
txtIsFullDate.Checked = bt;
txtAdrs.Text = dt.Rows[0][8].ToString();
txtCity.Text = dt.Rows[0][9].ToString();
txtState.Text = dt.Rows[0][10].ToString();
txtPin.Text = dt.Rows[0][11].ToString();
string country = dt.Rows[0][12].ToString();
Hi Pinal,
it’s a really helpful write up on getdate()…oit helps me alot…i am workin on a script that gives previous date in output only…
select dateadd(dd, datediff(dd, 0, getdate())-1, 0);
this query gives me the output in 2011-03-08 00:00:00.000 this format…
can u help me to get the output in “08-03-2011″ this format…
Abhijit
Where do you want to show formatted dates?
Sir… You are really a genious in SQL.Your SQL queries provide immense help to all. But i do have a question?
Can you give me the SQL query to retrieve data based on current date and time where my database table has two seperate fields for Date and Time whose data types are date and time(0) respectively. I am stuck in this query. I need to retrieve data from database.
And at the same time i need another query to add time of 3 hours to the current time and retrieve those datas from database.
Any help will greatly be appreciated
hey this solutn was vry helpfull to me thank u soo mch :)
select convert(varchar(10),getdate(),104)
it will return dd.mm.yyyy format
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
return same result in case of varchar and datetime input
but
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
output varies in case of varchar and datetime input
below is the example
select convert(varchar(10),convert(datetime,’5/13/2011′,111),111)
Select convert(varchar(10),’2011-05-13 14:46:34.000′,111)
both statement return different result
Hi,
I want to retrieve the records based on the column ‘INSERT_DATE’ which is having the datetime format as ’2010-12-09 21:03:13.966′
Make sure to read this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
Hi,
How to faster way to retrieve the data through select statement?
And before going to partition what are the things need to remember?
1 Make sure the indexed columns are used properly in the SELECT statement
2 Read about it in SQL Server help file
Hello Friends,
Can any one help me quickly where am doing mistake in the below queries… If I run the first below query its giving me output in 4 to 5 seconds of time…
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate =”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'
But if I take out the query from parameter @sqlQuery and run it then its taking lot of time(approximately 4 to 5minutes)
Can anyone help me what is the right Query to get it without @sqlquery parameters
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(’0′ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc'
Your VARCHAR(10) should be at least VARCHAR(12).
SELECT CONVERT(VARCHAR(10),’Jun 6 2011 9:35AM’,111), CONVERT(VARCHAR(12),’Jun 6 2011 9:35AM’,111)
should prove my point.
how do i retieve only the date from my table which has a column say date of birth in the datetime format
select dateadd(day,datediff(day,0,datecol),0) from table
What if you have an actual field in a table that contains both date and time of an entry and you only want the date part? E.G. the field is timelogged and it contains the date/time in the format YYYY-MM-DD 00:00:00.000
How would I go about getting only the YYY-MM-DD part?
What is the datatype of the column? If it is datetime datatype, use
select dateadd(day,datediff(day,0,datecol),0) from table
Thank you :)
Hi,
I’d like to convert a datetime field to varchar in YYYY MMM DD format.
I’m expecting the same result as the following SQL:
select SUBSTRING(CONVERT(varchar(20),GetDate(),113),8,4) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),4,3) + ‘ ‘ + SUBSTRING(CONVERT(varchar(20),GetDate(),113),1,2)
— output: 2011 Aug 18
Is there any easy way (something like SELECT CONVERT(VARCHAR(12), GETDATE(), 106)) of doing this without substring?
Thanks in advance.
Sri
The correct way of doing this is to do at front end application
If you want to do it by sql, use
select datename(year,getdate())+’ ‘ +convert(varchar(6),getdate(),9)
Thank you, Madhivanan. I need to do it in the SQL.
The SQL is very simple and working fine.
Thanks again,
Sri
I tried both solutions:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
And I got better performance in option 1.
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
I get 3 second executing the query with option 1 and with option 2 was around 5 second.
It is becuase of VARCHAR convertion. SQL Server takes extra time to do that.
i have problem with accending date…. my current syntax is
SELECT * FROM mytablename ORDER BY datefield ASC. right now my datefield using VARCHAR…
my result are :
01/02/2009
03/01/2009
04/06/2009
05/03/2009
06/12/2008
07/02/2009
result should be :
06/12/2008
03/01/2009
01/02/2009
07/02/2009
can anyone teach me the simple way to solve this problem and give some help please…. ^_^
Try this
SELECT * FROM mytablename ORDER BY cast(datefield as datetime) ASC
Also always use proper DATETIME datatype to store dates
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iv.aspx
this is very helpfull :)
A Lot quicker: Convert(DateTime, convert(int, GetUtcDate()))
Ok, I guess not so short:
The convert function is rounding the value. So dates that has passed noon will become tomorrows dates — oops.
thus correct value would be:
Convert(Int, convert(float, GetUtcDate()))
Converting from float to int will truncate the value.
This is most reliable method
select dateadd(day,datediff(day,0,getdate()),0)
how to convert nvarchar data in the format dd-MM-yyyy into datetime or smalldatetime while runtime?
select convert(datetime,your_nvarchar_date,103)
how to select only date part
when selecting two dates using Between clause
How to select only date part without time..can some one help me.
Thanks.
What about using the numerical rappresentation of a DateTime?
CONVERT(INT,GetDate()) give the date part of datetime rappresentation.
So my suggestion is to use CONVERT(DATETIME, CONVERT(INT,GetDate())).
Another advantage to integer cast versus string cast is that it’s simpler to use in ordering, filtering and so on (think about BETWEEN).
I have a vachar field in DB to store dates (which is actually creating problem).Now i can’t change the field type as i contains real data and lots of other application uses this DB. 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’
thnx its done…
What is the dateformat? Depending on it you can convert it and compare
where cast(col as datetime) between datefrom and dateto
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
hi can someone help me get date values in my table
i inserted 10-mar-05 but system gave me 1900-01-03
Thanks
i got it.
how i select date value from sql?. I used ms acces i used ‘ datevalue’ key word. but in sql ‘to_date’ key word is not working. any one please help me?. How i retrieve a date value in sql through java?. Please help me.
Hi i have a issue can you please help.
I have 35 K records (email address) i need to pull out only the domain part (after @) and update to another column.
How to do this?
Thnks a ton
Very Very Thanks..
Sir,
I want select data from table orber by register date in desc order and mssql register date field type is nvarchar.
what is query i need to use…
Please help me urgently….
Thanks
Sethu
Convert it to datetime in order by clause
ORDER BY cast(register_date as DATETIME)
Thanks alot.. its works fine…
Thanks for the site, I get many benefits.
I create a view that pulls down safety data from corporate site based on local id. I then need to derive the number of day’s since last incident based on certain codes and do other calculations. This will end up on ASP web page. Should I create another view or table or maybe even do sql code in ASP? Here is my select:
SELECT *
FROM [SAFETY_WEB].[dbo].[vw_HSEL]
WHERE (inrCode = ‘HS3′) OR (inrCode = ‘HS4′) OR (inrCode = ‘HS5′) OR (inrCode = ‘HS6′) OR (inrCode = ‘HS7′) OR (inrCode = ‘HS9′)
ORDER BY EventDate DESC, inrCode
Not sure how to get number of day’s since last incident and put it in a field
SELECT CONVERT(VARCHAR(10),GETDATE(),111) – EventDate of Top 1
Because of the ORDER BY above, the Top EventDate is the latest incident.
There is a table “Leave”
in which three columns are there namely
1st – EmpNo
2nd — From Date
3rd — End Date
Employee No. 1 has taken leave from 5th march to 10 march
so record will be
1 5-MAR-2012 10-Mar-2012
i want result in vertical
like
EmpNo Date
1 5-MAR
1 6-MAR
1 7-MAR
1 8-MAR
1 9-MAR
1 10-MAR
Can I comapre date fileld (with Getdate)without using Convert or cast method which will work faster than Convert/Cast ?
very waste blog ;; noting gets in to learner memory
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?
Use this
select cast(Getdate() as date)
selection criteria in both date format yyyy-MM-dd or yyyy-dd-MM in sql server
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I try this my some SQL Query it worked thank you very much.
If this is for formatting purpose you should use format function in front end application
why not u all simply use !!!!!!!!!!!!!!!! what the rocket science in this ..?????
select CONVERT(date, getdate())
Thanks Common Sense.
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.
Hope this COMMON knowledge will make SENSE to you.
Kind Regards,
You replied me coz it hurts u..why 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? April 17, 2012 at 5:40 pm
Thanks for pointing. I answer through emails, facebook, twitter or blog – many different way. Thanks for understanding.
Its for SQL 2008
hmm..v quick reply..thats good :) i forget to add it that its for 2008..actually saw the last comment which was of 28th may 2012 n using your 2007 technique :P
No Issue Common Sense!
I do my best as much as I can.
Yes, exactly, and for the before, I just deducted from official doc CONVERT(date,DATEADD(d,-1,GETDATE()))
http://msdn.microsoft.com/fr-fr/library/ms188383.aspx
Thx.. I need to update the part of the date my date is 02/05/1987 i am having 40000 records.. kindly suggest some solution… my all dates are random and i want make them look like as 01/month/year… means just need to set the day as 01 (update table set day(date.)=01 gives syntax. error…
This is what you need
update table
set col=dateadd(day,datediff(day,0,datecol),0)
where
thank u….
thnx madhivanan but in my case this will nt wrk.. i am having some dates like
2012-01-10 00:00:00.000
2013-01-18 00:00:00.000
2014-01-22 00:00:00.000
2015-01-17 00:00:00.000
2016-01-18 00:00:00.000
2017-01-29 00:00:00.000
2018-01-01 00:00:00.000
2018-05-15 00:00:00.000
2020-01-20 00:00:00.000
2021-01-01 00:00:00.000
2022-05-12 00:00:00.000
2023-03-25 00:00:00.000
2024-06-06 00:00:00.000
if i run this query on these will nt b changed.. as i need to convert these dates as year+01+01 for alll
So you want to reset to Jan 1?
sir could you help me in converting time which is stored as BIGINT, to date format?
I have date 2012-07-09 08:07:02.360 in database i want only ’2012-07-09′ this part will return in select statement. Can you help me ?
This result fulfill my requirement for difference between start time and end time
SELECT DATEDIFF(minute,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 08:00:00.000′)),(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′)))
Diff between current date time stamp and today other time:
SELECT DATEDIFF(minute,current_timestamp,(SELECT (CONVERT(VARCHAR(10),GETDATE(),111)+’ 15:30:00.000′))) AS DiffDate
When i am not passing date value . The database is taking default value 1900-01-01 00:00:00.000 .How to restrict this. i wnt to pass NULL value when i am not assigning any date.
Any Suggestions ?
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.
1 2012-07-05 17:09:41 1 0 1 0
1 2012-07-05 17:09:49 1 0 1 0
1 2012-07-05 17:09:51 1 0 1 0
2 2012-07-05 17:11:23 1 0 1 0
2 2012-07-05 17:11:35 1 0 1 0
3 2012-07-05 17:15:41 1 0 1 0
3 2012-07-05 17:15:43 1 0 1 0
1 2012-07-05 17:19:51 1 0 1 0
1 2012-07-05 17:19:55 1 0 1 0
this is the txt format
Question is
How to insert the txt file values to sql server table with time and date separately using stored procedure.
Import data into staging table;split date and time seperately;insert them into target table
Hi sir,
I have used this query ORDER BY cast([Regdate] as datetime) DESC but error occur was The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range
please help me.
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.
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.
Please help me urgently
Thanks in adv….
What is the datatype of [date]?
That date datatype was nvarchar sir,
Thanks
Thank you, for a quick tip.
Not really agree.
The best would be
CONVERT (date, GETDATE())
http://msdn.microsoft.com/en-us/library/ms188383.aspx
You may be interested to know more methods http://beyondrelational.com/modules/2/blogs/70/Posts/17608/different-ways-to-remove-time-part-from-datetime-values-faster-methods.aspx
if the date is stored as varchar datatype and i need to extract only “year” from that date,could anyone help me out in this issue?
very helpful Thank You……….
[...] Retrieve – Select Only Date Part From DateTime – Best Practice [...]
thanks :)
Thank you so much Sir, it help me lots
Sir, I am raghu i am having problem with this query
Q. Display the orderID 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 Function
i am not getting the proper output
i am using SSMS 2005, Database Adworks please reply ASAP.
Say big thank you for your help!!
Guys check this in SQL
Question:”print (getdate ())”
Solution:Mar 9 2013 5:56AM