There are three ways to retrieve the current datetime in SQL SERVER.
CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced.
GETDATE()
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.
{fn Now()}
The {fn Now()} is an ODBC canonical function which can be used in T-SQL since the OLE DB provider for SQL Server supports them. {fn Now()} can be used to print the current date and time every time that the report is produced.
If you run following script in Query Analyzer. I will give you same results. If you see execution plan there is no performance difference. It is same for all the three select statement.
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO
Performance:
There is absolutely no difference in using any of them. As they are absolutely same.
My Preference:
I like GETDATE(). Why? Why bother when they are same!!!
Quick Video on the same subject
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL (All Definitions)
Actually i worked in Oracle 10g. Now i got prj in Sql Server 2005. Here i feel little difficult in formatting Date.
Can u tell me how to format Date as ” DD\Mon\YYYY”.
Here in Sql Server 2005 gives both DateandTime.
can you help me with retrieving the the most current date and counting it? For example if 10 request came in today i want to display 10.
I am using MSSQL version unknown. I am also programming in PHP, thanks. Any help will b greatly appreciated.
select top 1 dateadd(day,datediff(dau,0,datecol),0) as datecol,count(*) from table
group by dateadd(day,datediff(dau,0,datecol),0)
order by 1 desc
SELECT TOP 1 COUNT(*) As DateCount
FROM [table name]
GROUP BY [date field]
HAVING [date field] = MAX([date field])
ORDER BY [date field] DESC
can you help me if there is any Facility or command in SQL Server 2005 ..
to Execute the stored procedure on particular time span..
I want to execute stored procedure /Function @ 00:01AM (Automatically) on daily basis..
i m using ASP.Net
I m fresher …Plz kindly tell me if possible..
Hi Vinod,
You can create an individual job for this under “SQL Server Agent” option.
Create one new job there and then you can mention your query like
Exec [Dbo].[SPName].
After this you can schedule its date and time as per your requirement. And then your job will be execute accordingly.
Thanks,
Yash Thakkar
Hi Ramkumar,
Refer my article SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice
Regards,
Pinal Dave (SQLAuthority.com)
I want just be able to get current year in sql server
to get current year, I combine YEAR with GETDATE = YEAR(GETDATE())
Hi,
Don’t feel that i’m appreciating Oracle. i’m New for Sql Server.
Just now i worked u r example.
Actually i need a Date in this format ( 25-Jun-2007).
In Oracle we use as follows,
Query:
select to_char(’25/06/2007′,’dd-Mon-YYYY’) from dual;
Result:
25-Jun-2007
In Sql-Server 2005, i tried following query.
Query:
select substring(convert(varchar(10),getdate(),111),len(convert(varchar(10),getdate(),111))-1,2) + ‘-’
+ substring(convert(varchar(20),getdate(),0),1,3)+’-'+substring(convert(varchar(10),getdate(),111),1,4)as Dat
Result:
25-Jun-2007
See the Length of the query, can u give me a short query to format Date in Sql-Server 2005
Regards
S. Ramkumar
’dd-Mon-YYYY’(wrong)
‘dd-MMM-yyyy’(Right)
Note that the example was how it was represented in ORACLE
In Oracle ’dd-Mon-YYYY’ is the correct format
SELECT REPLACE(CONVERT(VARCHAR(11),GETDATE(),106),’ ‘ ,’-') [Dat]
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Hi Ramkumar,
I prefer SQL Server over Oracle cause several reasons. One of them is simplicity.
First you should go over to post my previous comment.
Second see the reference of Book On Line.
which is BOL Convert. There are many examples of different date formats. Just use the one you need.
Regards,
Pinal Dave(SQLAuthority.com)
Hi,
Thankyou Mr. Pinaldave. i saw that on-line book. There are lot of styles were given for Date conversion. I think style no: 105 and 106 will satisfy my requirement.
Thankyou again. Bye.
Regards
S. Ramkumar
Hi, am learning SQL SERVER 2005. How i do condition in Date like
SELECT…
FROM…
WHERE EndDate < …………….
I have try currentdate, SYSDATE but it error. Please provide a code of Current date thank.
Didier Peinke
Hi,
Use GetDate() to retrieve or select current datetime.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hello
I have a table in sql server 2005 which contain a column for date.
I want to put a validation that if user does not specify the date, it takes current date and time.
Is it possible by setting the ‘Default value’ for that column?? if yes how can i dothat.
Please help me.
Thanl you
Jigna
how do get day of Date?
eg: monday, friday
select datename(weekday,getdate())
thank you very much for your answer. I was looking to insert year in a sql server table so i got the solution
@Jigna,
You can create default constraint on any column with default value as getdate()
@Mani,
http://blog.sqlauthority.com/2007/07/23/sql-server-udf-get-the-day-of-the-week-function/
how to retrieve “yyyy” from database
select year(date_col) from your_table
select datepart(yyyy,getdate())
this is simplest compare to your query, try this
How do you think it is the simplest query?
how to retrieve sql database server
select @@servername
When I use ISQL to execute “select getdate()” or “select current_timestamp” or “select {fn NOW()}” the timestamp returned is “yyyy-mm-dd hh:mm:ss.ttt”. When I run the same “select” in ADO the timestamp is returned as “dow-mmm-dd hh:mm:ss timezone yyyy”….what gives? I need the timestamp exactly as it appears in ISQL…
Thanks
Steve
Select * from customert where replace(convert(varchar(20),date,106),’ ‘,’-') between replace(convert(varchar(20),’17-May-10′ ,106),’ ‘,’-') and replace(convert(varchar(20),’19-May-10′ ,106),’ ‘,’-')
it will work!!!!
this query does not retrieve data?
select *
from Main_Table
where Todays_date <= convert(varchar,datepart(yyyy,getdate())) + ‘-’ + convert(varchar,datepart(mm,getdate())) + ‘-’ + convert(varchar,datepart(dd,getdate()))
Try
select *
from Main_Table
where Todays_date <= dateadd(day,datediff(day,0,getdate()),0)
hi
how to get previous date query.
thks
where
Todays_date >= dateadd(day,datediff(day,0,getdate()),0) and
Todays_date < dateadd(day,datediff(day,0,getdate())+1,0)
I am trying to do something very simple, but can’t seem to get it to work. I am using a PHP include to update certain parts of my website. What I want to do is take the date in the start_date column add n number of days to it, then run the PHP script. Can anybody give me any help on this?
Thanks so much!!!!
Tran
How i give current date from system in SQL.
Use Getdate() function
Hi,
i was wondering if i can ask, if anyone knows how to reference the getdate() function in a table, in SQL express, from a vb.net form. My current query is
SELECT *
FROM table1
WHERE date = ‘textbox1.text ‘
this however does not work, but if i put the date in its binary date format in the textbox this works, i can select something, does anyone have any ideas about a conversion??…..
Thank you in advance.
ashlesh
What did you mean be binary date format?
Can you give examples?
hi, i need more queries about date and time.
i have a problem.
i) how to store only time with AM or PM notation in datetime type
ii) how display time with out milliseconds
i need immediate solution.
regards,
thanks.
Hi Lakshmi,
You may use the following :
SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14))
There might be a better way of doing it. Maybe Guru (Mr. Dave) can help us.
All the best.
Hi again Lakshmi,
You may also use the following :
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
Regards,
Vishwanath
Hi once again Lakshmi,
You may create a function to get the time part and can use the function wherever you want. Create a function as follows :
CREATE FUNCTION [dbo].[udf_GetTime] ( @myDateTime DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @RetDate VARCHAR(50)
SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @myDateTime, 100),7)
RETURN @RetDate
END
GO
– Use the function like this :
SELECT [dbo].[udf_GetTime](GETDATE())
Hi Pinal,
create table employee
(
emp_id varchar(5) NOT NULL,
fname varchar(20)NOT NULL,
lname varchar(30)NOT NULL,
job_id smallint,
hire_date DATETIME DEFAULT GETDATE());
insert into employee values(’00003′,’Joe’,'Wys’,’0001′,”);
in above table, i need to get current datetime, but unfornately I am not getting it….follwoing is the result :–
00003 Joe Wys 1 1900-01-01 00:00:00.000
What may be the reason ?
Nitin -
You are overwriting your default value when you specify ” as your last value. You can not supply a value if you want the default to kick in. When you supply ”, the system puts the blank datetime in, which is 1900-01-01 00:00:00.000. The correct insert would be
INSERT INTO employee (emp_id, fname, lname, job_id) VALUES (’00003, ‘Joe’, ‘Wys’, ’0001′);
This should keep the default value for the hire_date.
Yes. Empty string can’t be used as default
Also it would represented differently according to datatypes
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx
hi,
I want to some project on SQL please provide me information about it.
Thank you,
Shakeer Basha
Hi, please tell me how to get last month of current year in SQL Server?
select dateadd(month,datediff(month,0,gedate())-1,0)
select Month(Getdate())-1
This is incorrect. See what is the question about.Yours will return 0 for January month
Hi,
how to calculate LOP in payslip using sql server?
You need to give more informations to help you
Create this function and find the days in month and calculete LOP=DaysinMonth-LWP
Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
YEAR(@pDate) % 100 != 0) OR
(YEAR(@pDate) % 400 = 0)
THEN 29
ELSE 28
END
END
END
Create this function and find the days in month and calculete LOP=DaysinMonth-LWP
Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
YEAR(@pDate) % 100 != 0) OR
(YEAR(@pDate) % 400 = 0)
THEN 29
ELSE 28
END
END
END
hello sir, i am facing a problem in sql , i want to know the command which is used to know the no. of days where two dates were given.
for ex.
28/10/2008
10/11/2008
then what is the no. of days between these dates.
thanking you..
Use datediff function
select datediff(hour,date1,date2)
select Datediff(Day,convert(datetime,’28/10/2008′,103),Convert(Datetime,’10/11/2008′,103))
can u correct this query , bcoz it’s nt working
select datediff(hh,select hire_date from employee,getdate())
It should be
select datediff(hh,hire_date ,getdate()) from employee
sorry it should be
select datediff(dd,hire_date ,getdate()) from employee
hi
can you tell me how to retrive data from any table by using current date we will not give date in where condition but the table have date colum
eg
select * from abc where date=todaysdate.
system should take the todays date.
Use this code
where
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
I want extract only date and not time from getdate()
Try this code
select dateadd(day,datediff(day,0,getdate()),0)
how to add validity numbers of years in start date (datetimepiker1) and after add years output last date in (datetimepiker2)
Please very important in my projects
reply me
I upsized an Access application (with data) to SQL 2005.
Application works fine with the ‘date’ data that was uploaded, but not with new dates that are added.
The dates LOOK like they are in the same format, but there must be a difference.
If I run
select * from census
where censusdate= ’4/10/2008′
I get all the records returned.
If I run
select * form census
where censusdate= ’5/1/2008′
nothing gets returned, even though in the table there are 89 records with the date 5/1/2008.
These records were added with the following
UPDATE vth_census SET vth_census.censusdate = getDate()
WHERE (((vth_census.censusdate) Is Null));
(I’ve tried different date formats……05/01/2008, etc. All the possibilties work with the 4/10/2008 date, not with the 5/1/2008 date.)
I’ve narrowed down the problem to the date issue, but I can’t figure out the solution.
Any suggestions?
Thanks,
Jill
Becuase you enter dates in ambigious format
Express the dates in YYYYMMDD format and it will work perfectly
hi,
i have one query,
i want to find the difference between the time saved in data base and current time
and the condition is
if the difference is > 30 the respected rows only should display.
Example
select columns from table
where datediff(hour,date_col,getdate())>30
Hi,
I want the day of a given date. Like Monday, Tuesday. There is a similar question, but no answer. Pls help
Nanthu
Use this code
select datename(weekday,getdate())
Hi There, when asking for the date/time difference between 2 dates, is it possible to get it formated in hh:mm:ss but the hours incur +24 per day.
ex.
2008/5/6 04:56:55 PM – 2008/5/5 03:22:25 PM
I need it to return 25:34:30
instead it returns 1901/01/01 01:34:30
Hi, i need de getdate from my local computer..when i use the getdate() bring me the date from the server…
Tks…
It is not possible to get local system’s date
Hello,
I need to format the date in that way:
20080506
it is yyyymmdd and with the sentence that I have tested:
DECLARE @day as DATETIME
SET @day = convert(varchar,datepart(yyyy,getdate())) + ‘’ + convert(varchar,datepart(mm,getdate())) + ‘’ + convert(varchar,datepart(dd,getdate()))
I get this: 200856, without zeros.
Even defining each varchar length (varchar(2)) I get the same.
What should I do?
Thanks!!!!
I have found the solution to my question. I didn’t know that there was a list of formating stiles.
To get 20080507 the code number is 112.
SET @data = convert(varchar,getdate(),112)
Thank you anyway
Read about Convert function in SQL Server help file
You will know lot of formatting styles
here you go Teo
SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + RIGHT(’0′ + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)),2) + ’0′ + RIGHT(’0′ + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)),2)
Hi,
I need to find the current year for the previous year
For ex,
Select yr_mnth,…
from …
where yr_mnth in (200603,200602,200601)
consider the year 2006 is the previous year…and I want the results for 2007(i.e,for current year)
result
200703 …
200702 …
200701 …
Did you mean this?
select dateadd(year,-1,getdate())
Hi..
I am new to this one… plz body can help me..
i have one query in sql like.. using the current year , i have to get the data previous year as well as current year…
what should i do?
Thanks!
select columns from table
where date_col>=dateadd(year,datediff(year,0,getdate())-1,0)
hi im vicknesh here , im havinga difficulty when exract the date according date , where i need to extract the member details which includes member expiry dates on 31stmay 2008 .
can i put as follows … WHERE (vip.vip_expirydate => {d ’2008-05-31′})
What happened when you tried?
If dates included time too, use
WHERE
vip.vip_expirydate >= '‘20080531' and
vip.vip_expirydate < '‘20080601'
Note that YYYYMMDD format is unambigious
how I can select all record in last week
table action
{
Actiondate datrtime
………
primary key(Actiondate)
}
where actiondate>=dateadd(week,datediff(week,0,getdate()),0)
I have one column which contains Date field. and I want date difference between two rows like (1st row- 2nd row),(2st row- 3nd row) 3-4,4-5 like that.
Hi Everyone
can any one find solution for this qury
I have one column which contains Date field. and I want date difference between two rows like (1st row- 2nd row),(2st row- 3nd row) 3-4,4-5 like that.
Thanks in Advance
VishwanathReddy
Hi
can we get only time in datetime(),if anyone knows plz update………
ex:
I need it to return 01:34:30
instead it returns 1901/01/01 01:34:30
Thanks
Kathir
Hi S. Ramkumar,
u can use convert function and u will get the value as u want
convert(datetime,getdate(),101)
Hi Kathir,
Can u please use this ?
convert(datetime,getdate(),108)
Hi esam,
can u plz use between clause?
Hi kpn,
can u please use datepart function and thn use ‘=’ condition.
datepart(yyyy,date)
can u tell me, how to dispay time like this “1994-11-05T08:15:30-05:00″ corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time.using sql server 2005
Thats the formation issue which should be addressed in your front end application
Hi,
can any one help me Please. i have a date column “DateTime” populated with 2008-05-01 11:45:58.380 with thousands of dates , when i run report /Excute i would like to get result ” DataTime” as 2008-05-01 only, not 2008-05-01 11:45:58.380. Is there any way to truncate the date field??
Have a look at Convert function in SQL Server help file. Also formation is the job of front end application
Hello,
I have entered this trying to get the current date into my table with the field named InvoiceSentDate. Is this the correct usage b/c the column keeps coming back null.
select @InvoiceSentDate = GETDATE()
Thanks,
Nathan
Can you post the full code you used?
This worked for me: –
SELECT dbo.AuditTrail.ATTime,
CONVERT(char(12), dbo.AuditTrail.ATTime, 3) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 8) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 10) FROM dbo.AuditTrail WHERE CONVERT(char(12), dbo.AuditTrail.ATTime, 3) = ’01/05/08′
WHERE dbo.AuditTrail.ATTime= �'
is the effecient method
My above reply should be
WHERE dbo.AuditTrail.ATTime= '20080501'
is the effecient method
Hello,
I would like to format the CURRENT_TIMESTAMP. Suppose CURRENT_TIMESTAMP return ’2008-02-09 11:46:17.827′. But I want ’09-02-2008′. Is it possible.
With Regards.
Biplab
It is the job of presentation layer
If you dont have any other option, use format function
select replace(convert(varchar(10),CURRENT_TIMESTAMP,103) ,'/','-')
Dear Friends,
I want to find only the differnece in the time in minutes and hours can u suggest me how can I do it
Read about datediff function in SQL Server help file
Hi Kabir/Biplab,
Did you guys find any solution to the problem.
Thanks,
Sanjana
Hello, i have a problem witn a stored procedure which uses datetime variables. The procedure looks like :
BEGIN TRANSACTION
DELETE FROM dbo.station
WHERE stationId = @stationId
AND auditDate = @time_stamp
auditDate and @time_stamp are both DATETIME
When I try to execute the procedure, it gives the following error : “Error converting data type nvarchar to datetime”
If i try to use another date format , like mdy not dmy , there is no row updated. Any idees ?
Thanks
How did you assign value to the variable @time_stamp?
Post the full code you used
@tylo
Its not the problem of datetime format, the problem is with Nvarchar and datetime. Either auditdate or stationid must be nvarchar datatype and you are trying to compare a datetime with a nvarchar datatype… which is not possible.
Try using a convert function and then compare, it would be helpful if you post your complete query….
Thanks,
Imran.
i am struggling from one concept.let me any one help for me?
how to find difference b/w two dates with timings….
@ sathish,
select datediff ( dd, ’01.23.1985′, ’02.23.1985′)
Result: 31
Hope this helps.
Thanks,
Imran.
Note that you should always express dates in unambigious formats like YYYYMMDD. Othwerwise depends on the Server’s date format, you may get error
hi all
Can anybody help me out to calculate the age of a person…in
years-month-days like 23 years-9 months-10 days
we are having a column as DOB(date of birth) in one table….
I want to calculate using current date….
thanx in advance
@jitender Pal,
I would use some thing like this for your question,
declare @date varchar(100)
set @date = ’2.23.2005′
select (convert (varchar(10), datediff (yy, convert (datetime , @date), getdate()))+ ‘ Years- ‘ + convert (varchar(10), datepart (mm, (getdate())- convert (datetime , @date) ))+ ‘ Months- ‘ + convert (varchar(10),datepart (dd, (getdate())- convert (datetime , @date) ))+’ Days’) AS DOB
You might want to change this code accordinly to your needs.
Hope this helps.
Thanks,
hi
I need to generate an expiry date baised on the date entered in one coloumn
can you help me out with this
[...] SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} [...]
hi,
I am new to sqlserver 2005, so pls help me
i have @date=10:10:10 AM (hh:mm:ss am/pm format) as varchar datatype.
I want to get the current sqlserver time as say @current_time and find the difference between @date and @current_time.
Thanks,
Prem.
select datediff(hour,@date,getdate())
how am i able to change the sql server current date… by adding 1 day to it?… pls help… thanks a lot… God Bless!!!
Did you try this?
select dateadd(day,1,getdate())
Hi
Can u please help
Iam having DateTime in database I need to compare only Time in the database with the current system time i.e comparing two times………………………
select datediff(hour,your_date,getdate())
I was going thru my sch project and this is a stupid course of mine~. Lectures don’t cover all these in detail
Thanks for the GetDate function with a () , I guess it helped me to search for more resource and info on the net too !!!
I want asp script which can help me to get a date after six month from date which is retrive from database.
where date_col>=dateadd(month,6,getdate())
How can i display the detail which is after the day 4th march 1963
i tried this one but shows no rows selected.
where as there are rows after this date
select * from agents
where to_char(activation_date,’FMMonth DD, YY’) > ‘march 4, 1920′
;
please help
Jen,
U can add one day by code below.
select dateadd(dd, 1, getdate()) – add one day
select dateadd(mm, 1, getdate()) – add one month
select dateadd(yy, 1, getdate()) – add one year
Same way you can subtract day, month or year. Just give negative value.
select dateadd(dd, -1, getdate()) – subtract one day
hi Kim,
Please see below:
select * from agents
where replace(convert(varchar, activation_date, 102), ‘.’, ”) > 19200403
how to give like % convert(varchar(10),dateadd(day,datediff(day,0,GetDate())- 10,0),103) % in sql query
can you help me to to get current date sql server 2005
use “select getdate()” to get current date
How to diplay the list of months in the current year ??
I want to know server date() in sql server 2005?
if i am not connect to server directly but i alreadt know server name
then how can is it possible???
No. You need to connect to it and query
select getdate()
how to auto generate email after one day or 24 hours?
urgent!!
help!
Hi Farhana,
you can maintain column in database which indicate status of email address generated or not.
For 24hr solution maintain one column which insert current date when data inserted in table.
you can query database and get last day records. After that say u have unique user name in your system or firstname or something that u maintain unique for all users. Take that field value and concatenate it with domain you want to auto generate like “hello@example.com” and send this details to user or update the data whatever task you want.
Hope this help you out.
Thanks
thanks!
Hai,
for example the manager when check the report , that time one hour before records only to display and it store automatcally in path.
What is the query for that one?plz tell me step by step
SELECT @DateTime AS Expr1, LastModifiedDate
FROM dbo.tblProjectHistory
WHERE (LastModifiedDate = @DateTime) AND (RecordId = 1)
LastModifiedDate fields type date time
LastModifiedDate=23/10/2008 2:18:51 PM
@DateTime=23/10/2008 2:18:51 PM
when run Query result null
the Answer to the getdate issue is simply to put Default
i.e
Insert into tableName(v1,va2,datetime)
values(‘myself’,'yourself’,default)
done.
i want select Second highest date from table.
Refer this
Apply the logic
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
Need help with getting the correct time due to daylight saving time. I need to get current time from table but factor in as follows:
If date is >= first Sunday in November and <= 2nd Sunday in March, then I need to subtract 1 hour from the date time I display on a report.
Ideas?
Hai Pinal dave,
I have an column with datetime, I want to get the seconds from that column.
For that what i could do
Thanks in advance
Hi Dhana,
you can use this: DATEPART(s,GETDATE())
Let me know if you want it in other formats too..
Thanks,
Tejas
hi ,
how to get the highest dates in the database
for example in my query i want to get the highest and compare to the input date
SELECT COUNT(*) AS Expr1
FROM Booking_Item
WHERE (’2009-03-06′ ’2009-03-06′)
This is my query
where your_date=(select max(date) from your_table)
how make report take date from text (from date to date)
only date between two text. thanks
Sir ,
I wnat only time in the Sqlserver2005
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005
Thanks
Shameer
Hi Arvind,
If you need to get Only Time, you can use
CONVERT(VARCHAR(10), GetDATE(),108).
For more details: http://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
Regards,
Tejas
================================
Biplab
Hello,
I would like to format the CURRENT_TIMESTAMP. Suppose CURRENT_TIMESTAMP return ‘2008-02-09 11:46:17.827′. But I want ‘09-02-2008′. Is it possible.
With Regards.
Biplab
================================
You can do like this:
LEFT(CONVERT(varchar, CURRENT_TIMESTAMP, 103), 10)
Hello i have 4 fields in my table
1.Market Name,
2.Commodity Name
3.Price
4.SubmissionDate Date_Format=(11/02/2009 01:58:27 )
i wanna fetch a collection of records in manner of such format:
one market ,all commodity and one recent Price submitted by user .
i try my my side but i can’t do such thing.
please help me.
Thanks and Regards
Do you know is there a way in SSMS to set up precision for showing how long does the query took? The one I see at the bottom in yellow?
for Lakshmi’s Q. here is a better formated “TimeOnly” function (tweaked Vishwanath’s)
CREATE FUNCTION [dbo].[udf_GetTime] ( @myDateTime DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @RetDate VARCHAR(50)
SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @myDateTime, 100),7)
SELECT @RetDate = REPLACE(@RetDate, ‘ ‘, ’0′)
SELECT @RetDate = REPLACE(@RetDate, ‘AM’, ‘ AM’)
SELECT @RetDate = REPLACE(@RetDate, ‘PM’, ‘ PM’)
RETURN @RetDate
END
Displays your time in HH:SS AM/PM format.
[...] SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} [...]
Is there a way to get the day as (sunday or monday ….) in sql server as we use to_char function in Oracle to get the current day.
use datename function
select datename(weekday,getdate())
[...] SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} Easiest way to get date time in SQL Server [...]
dear sir,
i am working in asp.net, i want to retrive Current Invitiondate of customere in result table.
so i use its query
select * from result where invitiondate = getdate()
But that query is not sucessfully result
plz send me this query
thanks
tejpal singh bajiya
select * from result
where
invitiondate >= dateadd(day,datediff(day,0,getdate()),0) and
invitiondate < dateadd(day,datediff(day,0,getdate())+1,0)
Use this query
select * from result where
Convert(varchar(10),invitiondate,103) = Convert(varchar(10),getdate(),103)
This will not make use of index
See my previous reply
Sir,
My current query:
select *
from workorder
order by timecreated
where timecreated >= DATEADD(day, -1, CURRENT_TIMESTAMP);
I want to retrieve all date with today’s date. I am not a SQL PRO, used to be but have not done SQL for over ten years.
“TIMECREATED” is the column where the work order’s creation date is stored in the format 1/14/2009 12:12:19PM
Can you please help me sort this out. THANKS!
Hi Peter,
you can use this:
select *
from workorder
where DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,timecreated))) >= DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())))
order by timecreated
Thanks,
Tejas
@Peter
select *
from workorder
order by timecreated
where timecreated >= CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1);
That’s if none are in the future. If there are future entries, BETWEEN is required:
select *
from workorder
order by timecreated
where timecreated BETWEEN DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1) AND DATEADD(d, 1, CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1))
@tejasnshah
Pinal’s best practices rule #5 is “Avoid functions on columns.” http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/
our solution puts a FUNCTION on the COLUMN itself.
Brian Tkatch,
I like very much your comment. Please continue to help community like this.
Kind Regards,
Pinal
sir,
im currently doing a project on rfid which tracks attendance. and i am having difficulty in creating an sql query statement to be used in crystal reports.
i am using 2 database tables
1)holds the employee attendance details retrieved from the rfid
2) general employee details, more like a database of employee details
attendance details:table contents
employee id
card id
employee name
department
systemdate
time in
time out
lost
mark delete
the time in and out values are provided by teh rfid kit
while the other details are retrieved from the employee details table.
im supposed to be generating weekly, monthly reports based on the above values.
and im finding it very difficult to learn querying withing such a short span of time .
my weekly reports should contain the columns
employeeid employee name no of days late no of days early out
the start day and end dates are provided by the user at run time using a datepicker in vs2005.
the report is supposed to show a consolidated value for no of days late and no of days early out columns during the time span provided by the user at runtime.
any person entering teh gate after 8:45 is marked late and any person leaving before 4:10 is marked early out
hence for each day that the person has left early or come late i have to show days in whole numbers as 1,2 ,3 etc for the above columns(early out and late in).. can u help me generate a query for teh above
Sir
I want to retrieve all the audit logs between previous date and current date.
How can i write this ??
where
date_col>= dateadd(day,-1,getdate()) and
date_col<getdate())
DEAR SIR,
MY QUESTION IS HOW CAN I TAKE THE SERVER DATETIME, NOT THE STATTION DATETIME.
I CREATE A PROCEDURE WITH A SELECT current_timestamp
to pass the timer but return the station time not a server time.
how i do this ?
thanks and best regards
It returns server’s time only
How did you know it returns your local system’s time?
I am using (right(CONVERT([char](8),getdate(),(108)),(10))) to get only the time part,however it is not showing the AM,PM part.How can I get the AM,PM part.
Please suggest.
Thank You
Somalia, look at the documentation on CONVERT to see what formats support am/pm. I see 100, 109, 130, and 131.
hi,
I want to fetch todays date from the whole database , not from any particular Table and that is already in database. please help me…!!!
did you mean this?
select getdate()
I think GETDATE() works more quckly than {fn Now()}
hi,
I want to find the difference between the current date and the date stored in the database(I used datetime datatype to store the dates in the database).
I m new to the sql server pleeeeeeeeeeeeese help!!!!
Use datediff function
select datediff(day,your_date_1,your_date_2)
Hi!
I need a bit of unique help.
Is there any way to retrieve the last updated records (for the last week say) if you DO NOT have the date stored anywhere in your table?
I know this is a dumb question but then again it’s kind of a silly mistake to make when creating the table! N what the hell… it’s worth a shot rite?
Could someone please tell me if they think this is at all remotely possible?
Without date or audit triggers this is not possible
am pretty new to sql queries…
in the below query
‘———————————–
SELECT USERS.USERS_NAME, COMPANY.COMPANY_NAME, LOGS.LOGS_DATETIME, LOGS.LOGS_ACTION
FROM LOGS INNER JOIN
COMPANY ON LOGS.LOGS_COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
USERS ON LOGS.LOGS_USERS_ID = USERS.USERS_ID
WHERE (LOGS.LOGS_COMPANY_ID = 11) AND (LOGS.LOGS_ACTION LIKE ‘uploaded%’) AND (LOGS.LOGS_DATETIME = GETDATE()) OR
(LOGS.LOGS_ACTION LIKE ‘downloaded%’)
ORDER BY LOGS.LOGS_DATETIME DESC
‘———————————–
—i need to filter records from the log table which as uploaded and downloaded in logs_action field.
this query should be run on the current date and teh cutoff time is 5.30pm for e.g. the query should run for today date and any file uploaded or downloaded before 5.30pm today should be shown.
thanks a ton
Hello,
I am trying to get the number of days between a date span.
If I have a span of three months I would like it to appear like the example down below. I would have the parameter as 01/01/2009 – 03/31/2009, but want the results split out. Can anyone help?
Jan xxhours
Feb xxhours
March xxhours
@Maegen
Look at DATEDIFF() to find the intervals. If you have difficulty, post what you have tried and perhaps we can help.
Hi,
I need to get details from attandence table
date,timein,timeout by chid id.
In this table stroring one day n times timein and time out. But i need day first time in and day last time out
plz send the result for this if any one know this is my mail id
Hello to everyone
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS SystemDate, LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS SystemTime
Just copy the code above to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY.
Sample Output:
SystemDate SystemTime
07/23/2009 3:59 PM
I hope you find it useful.
God Bless to all :)
Hello to everyone
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [SystemDate], LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS [SystemTime]
Just copy the code above to SQL Query or View to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY. There’s so many person want to know how will you format time in HH:MM AM/PM now here’s the answer. luckily i find it out. :)
Sample Output:
SystemDate SystemTime
07/23/2009 3:59 PM
I hope you find it useful.
God Bless to all :)
If you want UTC date time use:
SELECT GETUTCDATE();
Gareth.
Dear all
i would like query from my table but we have many data record the same infor but i need query only on colum
example depent only one type
in my table have
Row —mobile name—-type—–creae date—–install date
1 mobile1 A 03/05/2009 03/04/2009
1 mobile1 A 07/06/2009 07/06/2009
1 mobile1 A 05/07/2009 05/07/2009
1 mobile1 A 12/08/2009 12/08/2009
for user normall i use
mobile1 A 12/08/2009 12/08/2009
Pleae help me because we have more information
dear all
I want to make query for the same minute of transactions table, does any one help me
thanks
Hi,
I want to create database backup file with current date and time.
For example : database name is : RemoteDatabase
Current Date Time is : 10 August 2009 11:29 AM
How can I give this name to my .bak file?
Or if it is not possible can I provide time only like
RemoteDatabase_11:29AM.bak
Thanks
Vineeta
Refer this post
This is exactly what you needed
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/backup-database.aspx
Is it possible to put GetDate() or CURRENT_TIMESTAMP as a field’s default value? If yes, when I update a row, will this field update itself with the the timestamp of that update moment?
@Lucas
1. First question, Can Getdate() be used as default value to a column ?
Ans: Yes, GetDate() can be used as a default value for a column.
2. Second Question, when I update a row, will this field update itself ?
Ans: No. It will not update by itself, you will have to update this manually.
Consider below script as example.
create table #Example1 ( Eid Int, Ename varchar(40), Date_Time datetime default (Getdate()))
Insert into #Example1 (Eid, Ename) values ( 1, ‘SQL’ )
Insert into #Example1 (Eid, Ename) values ( 2, ‘SQQ’ )
Select * from #Example1
– Now update statment.
Update #Example1
Set Ename = ‘SQL’
, Date_Time = Getdate() — updating manually.
Where Eid = 2
Select * From #Example1.
Drop table #Example1.
How ever, this could be done automatically as well,
You can have a FOR UPDATE or AFTER UPDATE Trigger created on the table.
This trigger will fire when there is any update on the table, and inside this trigger, you can update your datetime column with the current datetime.
You can also use, if update statement inside trigger, to be specific, mentioning if there is any update for these specific columns only then update date_time column.
I dont suggest using trigger though, if you see many updates on the table. This could decrease your application performance significantly.
~ IM.
HI all,
can i retrieve the values for time between 5:00 pm and 8:00 pm from apptime column in database, does any one help me
Hi All,
Can anyone tell me how to find the difference of (year/month/day) birth date to till date.
i see the imran code but it is not perfect. I have to count actual year,month and date.
thanks,
Mazhar
@Kanika,
you can do that by using Datetime conversion functions available in SQL..
like
select convert(varchar(10),getdate(),105)
Can we select any of them as field’s Default Value if field type is DateTime?
Or
Is there any solution for that?
I need a column if it doesn’t specified on insert it’s default value should be current date time!
Create that column to have default value of getdate()
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & ms-access
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & in Ms-Access.
Formation matters only if you want to show dates in front end application. If you use front end application, use format function there. Otherwise read abot CONVERT function in SQL Server help file
Converting UTC datetime to a desired format like 2009-01-08 18:26:54.923 instead of 2008-06-18T13:51:46.7-05:00
Thanks for your post.
BTW the only one of the three that works within a DTS package against a legacy KB-SQL ODBC datasource is {fn NOW()}.
I’m a newbie, and am guessing it is because the others aren’t understood by the KB-SQL’s flavour of SQL?
Anyway, very glad that I remembered your post (which I’d already referred to for straight MSSQL queries) and found that one out of three worked in the context of what I was doing.
So, while they’re all the same performance-wise, there is a crucial difference in breadth of application.
Thanks again.
hi
i have problem in inserting the record depending on the current date. if current date change then record has to be inserted automatically.plz tel me how to write trigger or jobs in sql server2000
Actually i’m finding that
CURRENT_TIMESTAMP is correct, it returns the full date and time :
2009-10-26 04:58:24
GEDATE returns always return a time of 0 ( the date is correct though )
example : 2009-10-26 00:00:00
Si in my case i prefered to use CURRENT_TIMESTAMP because i sort my news by time, and i might have several news an hour.
Pat.
<>
How is it possible?
GETDATE() will always display date along with time
Hi,
I need help on writing a query for both these scenarios :
1. The current date is equal to or greater than 92 days past a semester start date.
2. The current date is not greater than 92 days past a semester start date.
Semester start date is a column..
Thank you..
Clue
datediff(day,col,getdate())>=92
Hi,
I am new in sql server,
i have some problem ,
like when i write this code
select * from [some table] where
convert (datetime,’1/5/2009′,103′) >= getDate()
when return the record is lose some of them .
i hope help me .
thanks
The correct way od foing it is
select * from [some table]
where
date_col>= dateadd(day,datediff(day,0,getDate()),0) and
date_col< dateadd(day,datediff(day,0,getDate())+1,0)
Hi all,
I have table in which hiredate is column and i want to calculate, Using HireDate of an employee the time they have worked. Basically i want to calculate the difference between hire date and current date of my system.
Thanks a lot in advance.
Jay
print datediff(day, ’2009-10-01′, getdate())
will give you 27, because I used ‘day’ as the first parameter.
http://technet.microsoft.com/en-us/library/ms189794.aspx
Select Replace(Convert(Varchar,GetDate(),106),’ ‘,’-')
how to find last 15 days, last 2 month date
how to find day on end of month
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
for my report I specify the following:
DECLARE
@StartDate datetime,
@EndDate datetime
SET @StartDate = ‘2009-12-10 21:00′
SET @EndDate = ‘2009-12-11 07:00′
SELECT ……
WHERE Datecolumn >= @StartDate
and Datecolumn < @EndDate.
For the report, instead of specifying date and time ('2009-12-10 21:00'), I want the users to only specify the time (21:00). Which is the best way to do this ?
Hello Clive,
If you are using SQL Server 2008 then you can convert the date variables to TIME data type.
In earlier version use the following syntax:
WHERE Datecolumn >= CONVERT (VARCHAR(5), @StartDate,108)
and Datecolumn < CONVERT (VARCHAR(5), @EndDate,108)
Regards,
Pinal Dave
é o seguinte precisava de uma ajuda vossa , quero visuallizar registos somente da semana passada , ou entao de 8 dias atraz mas nao queria q aparecesse o dia actual
tenho usado estes commandos :
DATEPART(week, dvl.DATa) = DATEPART(week, GETDATE()) AND
DATEPART(year, dvl.DATa) = DATEPART(year, GETDATE())
e este :
dvl.data > = getdate () -8
[...] Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server 6) SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} 7) SQL SERVER – TRIM() Function – UDF [...]
below query returns no rows
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
convert(varchar(10),hmp_effectivefrom,101)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
i am using convert(varchar(10),getutcdate(),101) format
below query returns the rows what is the diff between both
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
convert(varchar(10),hmp_effectivefrom,102)<=convert(varchar(10),getutcdate(),102) and hmp_Discontinued=0
group by hmp_EntityId
i am using convert(varchar(10),getutcdate(),102) format
Have you tried this?
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock)
where
hmp_effectivefrom<=dateadd(day,datediff(day,0,getutcdate()),0) and hmp_Discontinued=0
group by hmp_EntityId
pls reply me as soon as possible
Hello M.Kamala,
Here you are converting two date values as strings. And strings are compared from left to right character by character. To explain I am taking two values as below:
hmp_effectivefrom = 10/20/2009
GetUTCDate() = 01/4/2010
Now when we compare these values using convert(varchar(10),getutcdate(),101) format, they are compared as:
10/20/2009 <= 01/04/2010
This is false and will return record.
When we convert these two values using convert(varchar(10),getutcdate(),102) format, they are compared as:
2009/10/20 <= 2010/01/04
This is true and record will be returned.
Regards,
Pinal Dave
dear pinal,
which can i use in my project? any other solution for the same.
currently i using convert(varchar(10),getutcdate(),101) it’s working 2009 records now 2010 then doesn’t return any record.,
Pls suggest me if any good idea for date filter
See my previous reply
please suggest, can i use below format query?
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
otherwise using below l
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
hmp_effectivefrom<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
No need to convert any value. Just compare without conversion as below:
hmp_effectivefrom<=getutcdate()
Regards,
Pinal Dave
thks pinal.
one query.
hmp_effectivefrom stored in future value also.
Ex:
date stored in table already
’2010-01-05 04:35:09.680′ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
’2010-01-05 04:33:01.197′ (sysdate)
Ex:
hmp_effectivefrom<=getutcdate()
'2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'
then this record is not displayed. but i want today all records.
for that i am using like below is it correct or not. have look
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)
Please help as soon as possible.
if ok means any difference between below
cast(convert(varchar(10),hmp_effectivefrom,102) as datetime)<=convert(varchar(10),getutcdate(),102)
cast(convert(varchar(10),hmp_effectivefrom,112) as datetime)<=convert(varchar(10),getutcdate(),112)
thks pinal.
one query.
hmp_effectivefrom stored in future value also.
Ex:
date stored in table already
‘2010-01-05 04:35:09.680′ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
‘2010-01-05 04:33:01.197′ (sysdate)
Ex:
hmp_effectivefrom<=getutcdate()
'2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'
then this record is not displayed. but i want today all records.
for that i am using like below is it correct or not. have look
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)
i expecting your solution.
Hi M.kamala,
Use the below expression:
hmp_effectivefrom < convert(varchar(10),getutcdate()+1,101)
Regards,
Pinal Dave
I would like to get the following comparison:
example 1: I would like to receive data 2 years back from current date.
How can I arange that?
Thanx!
Rob (The Netherlands)
where
date_col>=dateadd(year,-2,getdate())
How can I give this name to my .bak file?
Or if it is not possible can I provide time only like
RemoteDatabase_11:29AM.bak
Hi Jayan,
We can not use special characters for file/folder name. “:” is one of special character, so we can not give file name that you want.
What alternate is give file name like:
Test_20100112.bak
where 2010=year, 01=month, 12=date.
Thanks,
Tejas
SQLYoga.com
Hello friends
How I can have TIME format
(for example) 125623
in sql server 2000
Hi Pinal,
I am just a beginner to SQL server 2008 and learning sql commands, i am unable to get logic behand store procedures
could you please help me out and can you post me the syntax for getting last and fist day of the month if we pass current date using store procedures.
thank you.
Select
dateadd(month,datediff(month,0,getdate()),0) as first_day,
dateadd(month,datediff(month,0,getdate())+1,-1) as last_day
Madhivanan
More functions
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/01/undocumented-date-and-time-related-functions.aspx
Madhivanan
Hi Pinal,
I am new in SQL Server 2008.
I want to execute some stored procedure on particular dates(all column entries as dates).
How shd I do tht?
Please help me out.
Thanks.
Chetan
Hello Chetan,
What I understand is that you have table containing dates and you want to execute some stored procedure on that dates. If that is what you want to do then create a SQL Server Agent job to execute once daily. In the job check for the GETDDATE() in dates stored in table, if found then execute else skip it.
Regards,
Pinal Dave
hi pinal,
im having trouble inserting and retrieving dates. im using visual web developer 2008 and sql server 2005.
i would like to insert current date while in retrieving, i want to retrieve date(s) only.
can you help me out by showing me the format in query?
thanks!
When you show dates in .NET use fromat function there
Hi Pinal,
Yes, I was looking for that only.
My issue has been solved.
Thank you very much for your help.
Regards,
Chetan
Hi All,
I have the column called created date , i want to make the report, for every three month ,like”jan,feb,march”,….etc..,
when i use between operators i am not able to get , similarly ,
can u please help me in code ,
Regards
sateesh
Post the code you used
Your requirement is not veryclear
Hi
when i tried to find record which is greater than current date it show me all date record because it compare date with time thats why it give wrong record but i tried below which give me right record
string currentdate = DateTime.Parse(DateTime.Now.ToString()).ToShortDateString();
select * from tbltest where TrainingDate>=’” + currentdate + “‘”
Hi,
I want difference between two date days, months and year all required.
im doing proj in .Net (c#)..i want execution time of query..how do i get it ? Also plz gv tips on tuning of queries..
Is it possible to insert on column with date fields that insert automatical after other registry on the same table was inserted?
Regards,
Pinal,
I have a table which stores daily transactions and I’m trying to generate a report that captures only transactions less than two weeks from the run date of the report and using ztransactiondate = GETDATE()-14 which is not working to get my results. Any suggestions,
Thanks,
Raul
Hello Raul,
Use greater than (>) operator to get all transaction of last two weeks as below:
ztransactiondate >= GETDATE()-14
Regards,
Pinal Dave
Hello sir…
Good Morning…
I want to Display Birthdate of My Site User On My Home Page
That Information Give ago 3 Days
Example
My Client Name Is :- Sachin
His Birth DAte :- 05-12-1987
Today Date Is :- 3-12-2009
So, Today On My Home Page Display “Sachin” Name Nad His BirthDate…
Thanks
Regards
[Removed phone and mobile number]
– SQL Server T-SQL date and datetime formats – sql date / datetime format
– Date time formats – mssql datetime – sql server date formats – sql dates format
– MSSQL getdate returns current system date and time in standard internal format
– SQL datetime formats with century (YYYY or CCYY format)- sql time format
SELECT convert(varchar, getdate(), 100) — mon dd yyyy hh:mmAM (or PM)
— Oct 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) — mm/dd/yyyy – 10/02/2010
SELECT convert(varchar, getdate(), 102) — yyyy.mm.dd – 2010.10.02
SELECT convert(varchar, getdate(), 103) — dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) — dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) — dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) — dd mon yyyy
SELECT convert(varchar, getdate(), 107) — mon dd, yyyy
SELECT convert(varchar, getdate(), 108) — hh:mm:ss
SELECT convert(varchar, getdate(), 109) — mon dd yyyy hh:mm:ss:mmmAM (or PM)
— Oct 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) — mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) — yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) — yyyymmdd
SELECT convert(varchar, getdate(), 113) — dd mon yyyy hh:mm:ss:mmm
— 02 Oct 2010 11:02:07:577
SELECT convert(varchar, getdate(), 114) — hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) — yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) — yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) — yyyy-mm-ddThh:mm:ss.mmm
— 2010-10-02T10:52:47.513
– Without century (YY) date / datetime conversion – there are exceptions!
SELECT convert(varchar, getdate(), 0) — mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) — mm/dd/yy
SELECT convert(varchar, getdate(), 2) — yy.mm.dd
SELECT convert(varchar, getdate(), 3) — dd/mm/yy
SELECT convert(varchar, getdate(), 4) — dd.mm.yy
SELECT convert(varchar, getdate(), 5) — dd-mm-yy
SELECT convert(varchar, getdate(), 6) — dd mon yy
SELECT convert(varchar, getdate(), 7) — mon dd, yy
SELECT convert(varchar, getdate(), 8) — hh:mm:ss
SELECT convert(varchar, getdate(), 9) — mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) — mm-dd-yy
SELECT convert(varchar, getdate(), 11) — yy/mm/dd
SELECT convert(varchar, getdate(), 12) — yymmdd
SELECT convert(varchar, getdate(), 13) — dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) — hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) — yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) — yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) — mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) — yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) — hh:mm:ss
SELECT convert(varchar, getdate(), 25) — yyyy-mm-dd hh:mm:ss.mmm
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) — yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) — yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) — mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) — mm-dd hh:mm
————
– SQL Server date formatting function – convert datetime to string
————
– SQL datetime functions – SQL date functions – SQL datetime formatting
– SQL Server date formats – sql server date datetime – sql date formatting
– T-SQL convert dates – T-SQL date formats – Transact-SQL date formats
– Formatting dates sql server – sql convert datetime format
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’, DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’, RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘Month’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’, DATENAME(MM, @Datetime))
IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ‘MON’,
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX (‘Mon’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Mon’, LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘MM’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’,
RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’,
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’,
RIGHT(’0′+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
– Microsoft SQL Server date format function test
– MSSQL formatting dates – sql datetime date
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) — 01/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) — 03/01/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) — 1/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) — 1/3/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) — 1/3/12
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) — 01/03/12
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) — JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) — Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) — January 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) — 2012/01/03
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) — 20120103
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) — 2012-01-03
– CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,’YY.MM.DD’) — 12.01.03
GO
————
/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/
– SQL format datetime – - sql hh mm ss – sql yyyy mm dd
– Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)
– US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)
– ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)
– UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)
– German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)
– ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)
– ISO8601 format: 2010-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
————
– SQL Server datetime formats – Format dates SQL Server 2005 / 2008
– Century date format MM/DD/YYYY usage in a query
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(char(10), OrderDate, 101),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
/*
SalesOrderID OrderDate OrderDateTime
43697 07/01/2001 2001-07-01 00:00:00.000
*/
– SQL update datetime column – SQL datetime DATEADD – datetime function
UPDATE Production.Product
SET ModifiedDate=DATEADD(dd,1, ModifiedDate)
WHERE ProductID = 1001
– MM/DD/YY date format – Datetime format sql
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(varchar(8), OrderDate, 1),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
/*
SalesOrderID OrderDate OrderDateTime
75123 07/31/04 2004-07-31 00:00:00.000
*/
————
– SQL convert datetime to char – sql date string concatenation: + (plus) operator
PRINT ‘Style 110: ‘+CONVERT(CHAR(10),GETDATE(),110) — Style 110: 07-10-2012
PRINT ‘Style 111: ‘+CONVERT(CHAR(10),GETDATE(),111) — Style 111: 2012/07/10
PRINT ‘Style 112: ‘+CONVERT(CHAR(8), GETDATE(),112) — Style 112: 20120710
————
– Combining different style formats for date & time
– Datetime formats – sql times format – datetime formats sql
DECLARE @Date DATETIME
SET @Date = ’2015-12-22 03:51 PM’
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
– Result: 12-22-2015 3:51PM
– Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
– Result: Dec 29 2012 3:47AM
————
– SQL Server date and time functions overview
————
– SQL Server CURRENT_TIMESTAMP function
– SQL Server datetime functions
– local NYC – EST – Eastern Standard Time zone
– SQL DATEADD function – SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP — 2012-01-05 07:02:10.577
– SQL Server DATEADD function
SELECT DATEADD(month,2,’2012-12-09′) — 2013-02-09 00:00:00.000
– SQL Server DATEDIFF function
SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′) — 62
– SQL Server DATENAME function
SELECT DATENAME(month, ’2012-12-09′) — December
SELECT DATENAME(weekday, ’2012-12-09′) — Sunday
– SQL Server DATEPART function
SELECT DATEPART(month, ’2012-12-09′) — 12
– SQL Server DAY function
SELECT DAY(’2012-12-09′) — 9
– SQL Server GETDATE function
– local NYC – EST – Eastern Standard Time zone
SELECT GETDATE() — 2012-01-05 07:02:10.577
– SQL Server GETUTCDATE function
– London – Greenwich Mean Time
SELECT GETUTCDATE() — 2012-01-05 12:02:10.577
– SQL Server MONTH function
SELECT MONTH(’2012-12-09′) — 12
– SQL Server YEAR function
SELECT YEAR(’2012-12-09′) — 2012
————
– T-SQL Date and time function application
– CURRENT_TIMESTAMP and getdate() are the same in T-SQL
————
– T-SQL first day of week and last day of week
SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())
SELECT LastDateOfWeek = dateadd(dd,7 – DATEPART(dw,GETDATE()),GETDATE())
– SQL first day of the month
– SQL first date of the month
– SQL first day of current month – 2012-01-01 00:00:00.000
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of the month
– SQL last date of the month
– SQL last day of current month – 2012-01-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
– SQL first day of last month
– SQL first day of previous month – 2011-12-01 00:00:00.000
SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of last month
– SQL last day of previous month – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
– SQL first day of next month – 2012-02-01 00:00:00.000
SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of next month – 2012-02-28 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))
GO
– SQL first day of a month – 2012-10-01 00:00:00.000
DECLARE @Date datetime; SET @Date = ’2012-10-23′
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))
GO
– SQL last day of a month – 2012-03-31 00:00:00.000
DECLARE @Date datetime; SET @Date = ’2012-03-15′
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
GO
– SQL first day of year
– SQL first day of the year – 2012-01-01 00:00:00.000
SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)
– SQL last day of year
– SQL last day of the year – 2012-12-31 00:00:00.000
SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,
DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))
– SQL last day of last year
– SQL last day of previous year – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
GO
– SQL calculate age in years, months, days – Format dates SQL Server 2008
– SQL table-valued function – SQL user-defined function – UDF
– SQL Server age calculation – date difference
USE AdventureWorks2008;
GO
CREATE FUNCTION fnAge (@BirthDate DATETIME)
RETURNS @Age TABLE(Years INT,
Months INT,
Days INT)
AS
BEGIN
DECLARE @EndDate DATETIME, @Anniversary DATETIME
SET @EndDate = Getdate()
SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)
INSERT @Age
SELECT Datediff(yy,@BirthDate,@EndDate) – (CASE
WHEN @Anniversary > @EndDate THEN 1
ELSE 0
END), 0, 0
UPDATE @Age SET Months = Month(@EndDate – @Anniversary) – 1
UPDATE @Age SET Days = Day(@EndDate – @Anniversary) – 1
RETURN
END
GO
– Test table-valued UDF
SELECT * FROM fnAge(’1956-10-23′)
SELECT * FROM dbo.fnAge(’1956-10-23′)
/* Results
Years Months Days
52 4 1
*/
———-
– SQL date range between
———-
– SQL between dates
USE AdventureWorks;
– SQL between
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN ’20040301′ AND ’20040315′
– Result: 108
– BETWEEN operator is equivalent to >=…AND….= ’20040301′ AND OrderDate < '20040316'
– SQL between with DATE type (SQL Server 2008)
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'
———-
– Non-standard format conversion: 2011 December 14
– SQL datetime to string
SELECT [YYYY Month DD] =
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' '+
DATENAME(MM, GETDATE()) + ' ' +
CAST(DAY(GETDATE()) AS VARCHAR(2))
– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638
SELECT replace(convert(varchar, getdate(),111),'/','') +
replace(convert(varchar, getdate(),108),':','')
– Datetime custom format conversion to YYYY_MM_DD
select CurrentDate=rtrim(year(getdate())) + '_' +
right('0' + rtrim(month(getdate())),2) + '_' +
right('0' + rtrim(day(getdate())),2)
– Converting seconds to HH:MM:SS format
declare @Seconds int
set @Seconds = 10000
select TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +
right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +
right('0' + rtrim(@Seconds % 60),2)
– Result: 02:46:40
– Test result
select 2*3600 + 46*60 + 40
– Result: 10000
– Set the time portion of a datetime value to 00:00:00.000
– SQL strip time from date
– SQL strip time from datetime
SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000
/* VALID DATE RANGES FOR DATE/DATETIME DATA TYPES
SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
DATETIME2 (8 bytes) date range (SQL Server 2008):
January 1,1 AD through December 31, 9999 AD
DATE (3 bytes) date range (SQL Server 2008):
January 1, 1 AD through December 31, 9999 AD
*******/
– Selecting with CONVERT into different styles
– Note: Only Japan & ISO styles can be used in ORDER BY
SELECT TOP(1)
Italy = CONVERT(varchar, OrderDate, 105)
, USA = CONVERT(varchar, OrderDate, 110)
, Japan = CONVERT(varchar, OrderDate, 111)
, ISO = CONVERT(varchar, OrderDate, 112)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY PurchaseOrderID DESC
/* Results
Italy USA Japan ISO
25-07-2004 07-25-2004 2004/07/25 20040725
*/
– SQL Server convert date to integer
DECLARE @Datetime datetime
SET @Datetime = '2012-10-23 10:21:05.345'
SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)
– Result: 20121023
– SQL Server convert integer to datetime
DECLARE @intDate int
SET @intDate = 20120315
SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)
– Result: 2012-03-15 00:00:00.000
————
– SQL Server CONVERT script applying table INSERT/UPDATE
————
– SQL Server convert date
– Datetime column is converted into date only string column
USE tempdb;
GO
CREATE TABLE sqlConvertDateTime (
DatetimeCol datetime,
DateCol char(8));
INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()
UPDATE sqlConvertDateTime
SET DateCol = CONVERT(char(10), DatetimeCol, 112)
SELECT * FROM sqlConvertDateTime
– SQL Server convert datetime
– The string date column is converted into datetime column
UPDATE sqlConvertDateTime
SET DatetimeCol = CONVERT(Datetime, DateCol, 112)
SELECT * FROM sqlConvertDateTime
– Adding a day to the converted datetime column with DATEADD
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))
SELECT * FROM sqlConvertDateTime
– Equivalent formulation
– SQL Server cast datetime
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))
SELECT * FROM sqlConvertDateTime
GO
DROP TABLE sqlConvertDateTime
GO
/* First results
DatetimeCol DateCol
2014-12-25 16:04:15.373 20141225 */
/* Second results:
DatetimeCol DateCol
2014-12-25 00:00:00.000 20141225 */
/* Third results:
DatetimeCol DateCol
2014-12-26 00:00:00.000 20141225 */
————
– SQL month sequence – SQL date sequence generation with table variable
– SQL Server cast string to datetime – SQL Server cast datetime to string
– SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
DECLARE @StartDate datetime;
SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+
RIGHT('0'+convert(varchar,month(getdate())),2) + '01' AS DATETIME)
WHILE ( @i = ’1997-11-01′ AND
RateChangeDate = ’1997-11-01 00:00:00′ AND
RateChangeDate < '1998-01-06 00:00:00'
GO
*/
————
– SQL datetime language setting
– SQL Nondeterministic function usage – result varies with language settings
SET LANGUAGE 'us_english'; –– Jan 12 2015 12:00AM
SELECT US = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'British'; –– Dec 1 2015 12:00AM
SELECT UK = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'German'; –– Dez 1 2015 12:00AM
SET LANGUAGE 'Deutsch'; –– Dez 1 2015 12:00AM
SELECT Germany = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'French'; –– déc 1 2015 12:00AM
SELECT France = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'Spanish'; –– Dic 1 2015 12:00AM
SELECT Spain = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'Hungarian'; –– jan 12 2015 12:00AM
SELECT Hungary = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE 'us_english';
GO
————
– SQL Server 2008 T-SQL find next Monday for a given date
DECLARE @DateTime DATETIME = '2012-12-31'
SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),
WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));
/*
NextMondaysDate WeekDayName
2013-01-07 00:00:00.000 Monday
*/
————
————
– Function for Monday dates calculation
————
USE AdventureWorks2008;
GO
– SQL user-defined function
– SQL scalar function – UDF
CREATE FUNCTION fnMondayDate
(@Year INT,
@Month INT,
@MondayOrdinal INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @FirstDayOfMonth CHAR(10),
@SeedDate CHAR(10)
SET @FirstDayOfMonth = convert(VARCHAR,@Year) + '-' + convert(VARCHAR,@Month) + '-01'
SET @SeedDate = '1900-01-01'
RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) – 1,
@FirstDayOfMonth)) / 7 * 7, @SeedDate)
END
GO
– Test Datetime UDF
– Third Monday in Feb, 2015
SELECT dbo.fnMondayDate(2016,2,3)
– 2015-02-16 00:00:00.000
– First Monday of current month
SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)
– 2009-02-02 00:00:00.000
————
Hi Pinal,
How can i fetch the current datetime using user defined function? I think we may not user getdate() function with in the user defined function.thanks in advance.
Create a view like
create view date_view
as
select getdate() as today
Now use this view in your function to retreive current date
Thanks Madhivanan.
Hello Pinal,
I want to write a function for daylight saving. can you tell me how to find out last sunday of month in sql 2000.
Regard,
Ajay
@Ajay
SELECT
DATEADD
(
d,
CASE DATEPART(dw, Next_Month.Date)
WHEN 1 THEN 7
ELSE DATEPART(dw, Next_Month.Date) – 1
END * -1,
Next_Month.Date
)
FROM
(SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0) Date) Next_Month;
datename(month,getdate()) as month
O/p: march
Note month name will come with this use it
i inserted 400 records in to sql server 2008 in particular day. after some day i realised that inserted data was wrong. how to delete that data.
@SIVA NANDA REDDY
If you have a date in the data
DELETE FROM table
WHERE date-col >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
AND date-col < DATEADD(d, DATEDIFF(d, 0, GETDATE()) + 1, 0);
Hiiiiiii
How can i get previous date of current year or how can we get current session in that format yyyy-yy plz help
@Amit
To get yesterday: DATEADD(d, -1 GETDATE())
For available date formats, please see the help fil on CONVERT()
http://msdn.microsoft.com/en-us/library/ms187928%28v=SQL.100%29.aspx
hello sir i am working on SQL that is on ORACLE server ….i want to insert current date and time in one of my tables..what should be its format…i tried the following..but htis is only giving me current system date..
insert into appliance_reading values(to_date(sysdate,’DD-MON-YYYY:HH12:MI:SSAM’));
please correct the following……and ur info is really valuable…thank u sir!
@Salma
Change SYSDATE to a date time string in the picture format listed. For example:
insert into appliance_reading values(to_date(’16-APR-2010:07:55:00AM’,’DD-MON-YYYY:HH12:MI:SSAM’));
It should be
insert into appliance_reading values(to_date(sysdate,'DD-MON-YYYY:HH12:MI:SSAM'));
Also note that this site is for MS SQL Server
For oracle questions, post at http://www.orafaq.com
My previous query should be
insert into appliance_reading values(to_date(sysdate,’DD-MON-YYYY:HH24:MI:SSAM’));
Hi ! i am new to this but i have a doubt regarding sql dates.
i am getting an old date from database and i have to change only the date to current date. i am doing this in java but facing problems with java.sql.date and java.sql.date.
Please help me!!
Can you give us more informations?
If you want to change it to current date, use getdate() function
I am executing query in sql server. Is it possible to bypass optimization module??? im using sql server 2005..
Is there any service to put off optimizer in sql server?? can it be done??
Dear All,
Hi i need a little help from u guys… we have an application where client wants to stop there users acess the application after 7.30 pm just they need to open the appication but they should not able to login the application untill admin gives permission……this is where i got struck kindly help me out guys
You chan check the condition based on current date
if getdate()>=dateadd(day,datediff(day,0,getdate()),'7:30')
Thanks,
You helped me a lot
Also refer this to know how Datetime column works in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
very good answer. it is very helpful for me.
Hi Everyone,
This is part of select statement query in Access and I am trying to replicate in SQL 2008 server and i am really struggling, Please can someone help
Here is the query.
Select fname,lname,
DateSerial(2010,[Month],[Day])-[S Date] AS [Days Late],
IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0") AS [0 - 2 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]14),”1″,”0″) AS [2 - 4 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]28),”1″,”0″) AS [4 - 6 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]42),”1″,”0″) AS [6 - 13 Week Marker],
IIf((DateSerial(2010,[Month],[Day])-[S Date]>91),”1″,”0″) AS [13 Week Marker]
from Tickets
Where
((DateSerial(2010,[Month],[Day])-[S Date])>14))
ORDER BY DateSerial(2010,[Month],[Day])-[S Date] DESC , IIf((DateSerial(2010,[Month],[Day])-[S Date]<=14),"1","0");
Hi Sohail,
Sorry I haven’t the time to look at the above at the moment, but to point you in the (hopefully) right direction, you are going to want to use a CASE statement and the CONVERT function.
Gareth.
Thanks for providing alternative.
I normally use GETDATE(). But never heard about SELECT {fn NOW()}.
I like your “My Preference:” section !!!
You can read about more such functions in this blog post
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/01/undocumented-date-and-time-related-functions.aspx
Your article is very good
i want to display todays visit in my program.
so tell me query for that in c# for sql database
Where
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
how to check user login in network using c# & sql.
how to find a day,hours and minutes from one query.
guys i want the OutPut Like this
as i’m selecting a query which have many crows i’m showing some sampl that i want
Days
————-
1 Days ago
8 hours ago
12 minutes ago
2 minutes ago
8 minutes ago
12 hours ago
please help me
select
case when datediff(day, MyDate, getdate())>=1
then cast(datediff(day, MyDate, getdate())as varchar )+’ day(s) ago’
else
case when datediff(hour, MyDate, getdate())>=1
then cast(datediff(hour, MyDate, getdate())as varchar )+’ hour(s) ago’
else
case when datediff(minute, MyDate, getdate())>=1
then cast(datediff(minute, MyDate, getdate())as varchar )+’ minute(s) ago’
else ”
end
end
end
from MyTable
You need something like this?
Dear All,
Hi i need help from u,i want to display a current date on my web application but it shows previous date upto 12.30 PM, after 12.30 PM as indian time its get change to current date .Please help me how to solve this problem.
Can you post some sample data with expected result?
Hi,
I have a table with 2 columns, one for date(only) and one for time(only).
I need to insert into those columns the current date and the current time.
Could you help me please?
I got it:
insert into MyTable
(MyDate, MyTime)
values
(dateadd(day, 0, datediff(day, 0, getdate())),
getdate()-dateadd(day, 2, datediff(day, 0, getdate())))
It seems that for some reason if the datetime is 12/30/1899 10:20:00, the date becomes invisible, only time is shown, like :
MyTime
=====
10:20:00
What is the datatype of the columns?
Hi,
datetype of the columns is datetime.
I am looking for function that returns the data between two times
for ex. mod_date_time between ’2010-07-21′ and ’2010-07-22′ will give me the data which has 21/07/2010 date.
But if I want to see the data between 06:30 am to 09:00am, How do i do this?
Please help me out, with this one!!!
Make sure to read this post and try to understand hot to use date and time values in the where clause
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
Hi Vikash,
You can use to write a query as
SELECT *
FROM Table
WHERE DATE BETWEEN ’2010-07-21 06:30′ AND ’2010-07-21 09:30′
This query will return only data between 06:30 AM and 09:30 for 2010-07-21
Thanks,
Tejas
You should always enter dates in unambiguous format. Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-ii.aspx
HI Pinal Dave,
how to have the query for data enter in one particular month and year like range 1st of every month to 5th of same month, 6th to 10th, 11th to 15th like wise
pls i have having serious problem with my application.whenever any transaction takes place,it records the date the transaction took place in this format,04/08/2010(mm,dd,yyyy).from the client end they are suppose to be able to view report for all transaction ,but the problem is that the rad date picker brings out the date in this format
4/8/2010 12:00am but i was able to use this code RadDatePicker1.SelectedDate.Value.ToShortDateString(); to get only the date.what i need is for the date value gotten from this rad date picker to be in the same format with the first one ,that is 04/08/2010 must be in the same format as 4/8/2010. i have tried using SELECT CONVERT(CHAR(10),4/8/2010,110) it didnt work.what sql code do i use.
Make sure to read this article that explains how to use proper date format and use it effectively in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-ii.aspx
I have 3 columns in a table(CreationDate, LastModifiedDate, presentDate). when i am inserting data into record i want system datetime automatically updated to the table.
how can i do that
You need to use getdate() function
Hi Rakesh,
You need to alter those columns as:
ALTER TABLE
ALTER COLUMN DATETIME DEFAULT(GETDATE())
By this way, if you not specify value for this column then also this column will be populated with current time.
Thanks,
Tejas
SQLYoga.com
Hi all,
i have a ITEM_ID,start date & end date. For the ITEM_ID, if the current date falls under the start date & end date ,it should return yes or else no.
How to achieve this?
Thanks in Advance
Thanks & Regards,
Priya
select ITEM_ID, case when getdate() between start_date and end_date then ‘yes’ else ‘no’ end from table
Hi All,
I want to convert MM-YY (for ex: 04-99) to YYYY-MM-01 (always first day of the month ex: 1999-04-01). Could anyone please advise?
Thanks in advance,
Sathish.
declare @date char(5)
set @date=’04-99′
select convert(datetime,’01-’+@date,5)
– =============================================================================
– Author : Venkata Narayana Thogati
– Create date : July 26 -2010
– Description : This SP for to get the day dates of perticular day in a month
– Parameters : @month, @year, @dayname
– Exicution : Exec [p_GetReqWeekDaysInAMonth] 01, 2010, ‘Monday’
– Result : It givies the all monday dates in jan month of 2010 year
– =============================================================================
ALTER PROCEDURE [dbo].[p_GetReqWeekDaysInAMonth]
— Add the parameters for the stored procedure here
@month int = 0,
@year int = 0,
@dayname varchar(50)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
SELECT
CAST(CAST(@year AS VARCHAR) + ‘-’ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number as DaysDate,
DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-’ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number) as DaysName
FROM master..spt_values
WHERE
TYPE = ‘P’
AND
(DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-’ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number) = @dayname)
AND
(CAST(CAST(@year AS VARCHAR) + ‘-’ + CAST(@Month AS VARCHAR) + ‘-01′ AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )
SET NOCOUNT OFF;
END
select cDate from tablename where month(CDate)=MONTH(SYSDATETIME()) and day(CDate)=DAY(SYSDATETIME())
Hai,
I get current time using the following select query,
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
Result : 4:00PM
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
Result : 5:20PM
I want the time difference and get result like this 01:20 using select query, plz tell anyone what sql query?
Do you want top take this from a table column?
hi i am a sap b1 developer.i entered time in a field.when i am retrieving the time when 00:01 it is showing as “1″ please tell me how to retrieve time value.
What is the datatype of the time column?
Hi All…
Ho can I convert sum of decimal to time
actually data in data base as fallows:
Col1 Col2 Col3
1.20 1.30 1.20
I tried to Sum(col1+col2+col3) = 3.70
but here 1.20 = 1hr 20min, 1.30 = 1hr 30min
I want result should be as 4.10, for this result what can I do?
please advice is there any userdefined function is there for this
Thanks,
Venkat919
Refer this post. The methods used will help you in solving this
http://beyondrelational.com/blogs/madhivanan/archive/2008/11/15/time-calculation-on-numbers.aspx
Date conversions are big pain to me. I always confuse on data conversion using “convert()” function.
hello sir
getdate result ime and date show
select getdate as currenttime
time and date will be show
but i have a one problem in create table
create table employee
(
emp_id int,
emp_name varchar(30),
emp_time datetime default ‘getdate()’
);
insert into employee tables(01,’happy’,”);
but output is different emp_time 1900-01-01
i want to say that default date and time come in emp_time
plz sir help me about this query
by
If you want default datetime to be added, omit that column during insertion
insert into employee (emp_id,emp_name) (01,’happy’);
How to get difference between two times for example :
’28/10/2010 00:05:45′ and ’28/10/2010 00:07:50′
i want to know how to get difference between only time ???
answer must be in the format : ’00:02:05′
select date1-date2 from table
Do the formation at front end application
Hello,
can i date populated with the table dd-mon-yyyy format ?
Yes with not as a datetime. Refer this post for more informations http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-ii.aspx
I want to insert a date as 19-Apr-2010 …..?
can i inserting this format in table?
Formation doesn’t matter. Refer this post for more informations about datetime
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
Hi All,
Pls any one help me.
I want to calculate, How to calculate Year to Date And Month to date Records.
Post some sample data with expected result
How to retrieve current datetime “2/16/2008 12:15:12 PM” from sql query “SELECT GETDATE()” will come “2010-10-21 15:25:31.437″
I need below format.
“2/16/2008 12:15:12 PM”
Can any one please help
Do the formation at front end application or use convert function
Performance:
There is absolutely no difference in using any of them. As they are absolutely same.
Very wrong. You said yourself, getdate() is a nondeterministic function and cannot be indexed in views.
It makes a big difference in views with large tables, where you use the date in a where clause to filter for a reporting date.
SELECT {fn NOW()}
is the one you should choose, and getdate() is the one you should avoid.
But this is very wrong.
You wrote yourself:
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed.
So if you have a view with a few joins, how do you think getdate() will influence the performance. I can tell you: getdate()’s performance is so very bad, you are a lot faster using the canonic ODBC escape sequence functions, such as fn now() …
dear sir , i m software trainee , i inserted system date and time in table but i cant update that systemdate and time , i cant identify problem please give me a solutions..
what query used in sql server to update?
Update table
se col=’your value’
Post the code you used
Pinal Dave-
Just a quick thanks, this post has helped me a couple of times as a reference!
Tom
I have a Sql Sever 2008 DB with a list of dates and events associated with those dates. I’d like to return only those events that are happening on the current date.
Something like
SELECT [event] FROM [new database] WHERE ([date] = @currentdate) ..I know this isn’t right.
Any advise would be appreciated
Hi,
Is there a way to get table record created date?
Hi Nadeeka
use this code ….
SELECT create_date
FROM sys.tables
WHERE name=’TableName’
satender
Hi Nadeeka
If u want get ‘creation date’ , ‘modification date’ for all tables then , u can use this …….
…………………………………………………………….
Select name as ‘Table Name’, create_date as ‘Creation Date’, modify_date as ‘Modification Date’ from sys.objects
where type_desc= ‘USER_TABLE’
………………………………………………………….
Satender
i have a sql server 2005 database.
on which a table cntains a filed datetime.
now i need to fetch value filtered by date. but when i am executing query, it returns NULL. i think sql server stores date as datetime field, thats why i am not getting actual resuls. Please help what i will do.
Indranil
Use this for converting ur date into 2011-02-22 in this formate
REPLACE(CONVERT(varchar, Your_date_variable , 111), ‘/’, ‘-’)
May it will help You…
How are you passing dates values to the query?
can you help me if there is any Facility or command in SQL Server 2005 ..
to Execute the stored procedure on particular time span..
I want to execute stored procedure /Function @ 00:01AM (Automatically) on daily basis..
i m using ASP.Net
I m fresher …Plz kindly tell me if possible..
You need to make use of job. Read about JOB in SQL Server help file
how to set the date modifiers to put the date with no year i.e. 12th March ??
IN MS-SQL which function is equivalent to mysql’s “UNIX_TIMESTAMP()”
There is no equivalent. You need to find out using a query
Hi,
Can someone tell me how to call a function/procedure from Oracle
Thanks
Sharath
Hi,
Can someone tell me how to call a function/procedure from Oracle in SQL SERVER
Thanks
Sharath
You need to make use of linked server or OPENDATASOURCE function
Dear Sir,
I create a login form in which if you insert password 3 times wrong then u r account is locked for next 15 minute.
and we send a random password on referenced emailid which is sent after 15 minute.
For these 15 minute the account isactive=’false’.
So can u please help me to give code for a stored procedure or triggers which is fired after 15 minute,which update this account isactive = ‘true’.
Please help me.
Thanks In Advance
When a user logins in, use this code
if exists(select * from table where datediff(minute,datecol,getdate())>15
update…….
Skaters take tests to quantify advancement in their chosen discipline. Upon completion of each of these exams, a congratulatory email will be sent to the skater to acknowledge his or her achievement.
Test data is stored in a database; this application will run daily. It will query the database for any tests completed the day before and will send emails to those who have completed their exams. It will also send a notification email to a Test Administer listing each of the skaters, the exam completed, and the date.
Requirements
Create a windows console application that retrieves data from a database and sends an email upon successful completion of an exam.
1. Application will be scheduled to run daily. Data retrieved will be from the day previous. (a SQL mdb file is acceptable)
2. Email body is to be in HTML format. Text is provided below.
a. Email will greet each skater by name
b. Email will specifically mention the Exam name
c. Email will indicate the date on which the skater passed the exam
d. Email will be ‘signed’ by the developer (See Text section below)
3. Administrator notification email body is to be in HTML.
4. Application must demonstrate at least two different exams (exam names)
5. Application will contain at least one user defined class. It is not acceptable to demonstrate the concept within the default Program class.
6. While demonstration of ability to send the email from the application is mandatory in lieu of actually sending an email, application will output to the console.
7. Application source will be sent in a compressed file to the person delivering these requirements for evaluation. Time spent in development should be included in the message or with the project.
need help with this. Thank you
I have a Special Offer Product table in this one Product name,
Offer Start date and Offer End date having column names
how to write a query to
when User enter a date
to get the Product Name when that date is must should between Start date and End date of that table
Please tell me how write the query the above the condition?
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
sir i have to select the month from the date like
20-Jan-11
select the Jan from it
plz help
select datename(month,date)
HIII
I want to set the default date/time in sql server without using anyquery.. plzzz help
Use default definition for that column
my application is host in USA means the server is in USA but i want datetime of client machine. my client can be anywhere in world. it is return datetime of server but i want client machine datetime. please reply me……
As long as you use proper DATETIME datatype and unambigious date format for date values, it does not matter. Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
hai ,
how can i get the date beyond the limit(i guess the limit is around 1753 ) that had been defined by the sql. and that should be useful for different functionalities .
One way is to store it in varchar column and handle it differently
how to convert UTC time to local time
“ALTER SESSION SET TIME_ZONE” is in oracle. what is the command
SQlserver
for changing the timezone from EST to IST
HI PINAL
“ALTER SESSION SET TIME_ZONE” is in oracle. what is the command IN
SQlserver
for changing the timezone from EST to IST
Hello Every one
How can i display Date in
20th July 2011 form in SQL SERVER
i know in oracle we used date format
DDth MON YYYY
Don;t know how to do in SQL SERVER 2008 .
Regards
Qazi
Look at the convert function in SQL Server help file
I am storing Created date and time in all the tables. The problem is that webserver and sql server is in US and it is storing US date time as the server is in US. How we will get indian date and time corresponding to US date and time
i have a table1 with columns empid ,name,age and other table2 with columns empid ,salary ,here in table2 empid used as foreign key
so pls anybody give me solution to find the highest salary of the employee
select t1.empid from table1 as t1 where empid=(select top 1 empid from table2 where empid=t1.empid order by sal desc)
want to know how can I get the YEAR,Month into database.. example .Suppose it’s August, 2011 now the output that i need is like this: CAB 11 08 001 .. (CAB + YEAR + MONTH + CURRENT NO. in tracking number. )
this is my sql ..
–ALTER PROCEDURE [dbo].[generateTrackNo] AS –Declare @tempYear VARCHAR(5),@tempMonth VARCHAR(5) –Set @tempYear = Year(GetDate()) –Set @tempMonth = Month(GetDate()) –SELECT ‘CAB’ + Right(Cast(Year(GetDate()) as varchar(10)),2)+ Right(Cast(Month(GetDate()) as varchar(10)),2) + Right(’000000′+ Cast(CurrentNo as varchar(10)), 5) from tblTrackNo where GenYear = @tempYear –UPDATE tblTrackNo SET CurrentNo = CurrentNo + 1 where GenYear = @tempYear the output for this is CAB1180035 .. i need CAB1108035 .. i need to put Zero(0) 08 like this for Month..
in my table i have only genYear and Current No. do i need to add another column for MONTH? thank you so much
Hello All
In oracle i can pass parameters to query in run time like
select * from dept where deptno =&deptno
when i run this query it prompts me for deptno at run time
.
how do i do this in sql server .
waiting for reply thanks in advance.
Yahya
You need to use a variable and assign the value to it
declare @depno int
set @depno=345
select * from dept where deptno =@depno
hii
i want to display only the time in sql server 2005
so what will be the query????
Version 2005 does not support time datatype. you need to use datetime datatype only
Hi,
i have a table in which date is a field,this value gets entered once the user specify date in front end, now
i need to intimate user after 3 months from this date stored?
how to do it ? Do i need to give separate field for it?Please help
You can use datediff function
if datediff(month,datecol,getdate())>3
–Do some stuff
I tried using datediff ,but it is not working .. so what to do?
i have declared date as varchar, is that a problem?
Yes. It will be a problem. You can not do any date related operations on varchar. You should always use proper DATETIME datatype. Refer this http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iv.aspx
I changed datatype to datetime, and tried with the code, but it does not work, please say what to do?
–Getting todays date record
select * from tablename where datecolumn between ’2011-10-25 00:00:00′ And ’2011-10-25 23:59:59′
Dont specify the date. It will work work if you run later. The general approcah is
select * from tablename where datecolumn>=dateadd(day,datediff(day,0,getdate()),0) and datecol<dateadd(day,datediff(day,0,getdate()),1)
Great article glad I found it when I did I would have been lost without it. Backlinks
A computer program called Antivi took over my computer. How do I get rid of it?
I will check the databas field date time format compare the current date time checking how to integrate plese help me
FWIW, I was connecting to a non-SQL DB (PROGRESS still lives!) while creating a DTS package through ODBC and it does make a difference regarding which one you use.
GETDATE() was giving me an error until I tried {fn NOW()} which worked like a charm.
Thanks to this page, I was able to find a solution though.
I have one datetime variable. It contains the value like {10/10/2011 2:33:33 PM}. How can i compare this value with the other datetime variable’s value which is like {2011-10-10 14:33:33.990} ?
This both are the same time but still i can’t compare it. Help me how to do it?
Thanks in advance,
Yash Thakkar
Make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hi,
How Can I Insert / modify current date time in TimeStamp Column.
when I try to update timestamp column, its giving msg
Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.
when i try to insert timestamp column, its giving msg
Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
Thanks in advance,
Vijay Vegi
You cannot add/update value for Timestamp datatype
hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.
How can create the date datatype in sql query.
greantly give me your suggesstion.
thanks,
Gomathi
hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.
How can create the date datatype in sql query.
greantly give me your suggesstion.
thanks,
Gomathi
Note that DATE datatype is available from version 2008 onwards. You can use datetime datatype instead
i want query which retrive date from close to curret date…example
5-12-2011
20-12-2011
take 20-12-2011
Do the data available in a table?
select max(date) from table
where date<=getdate()
How to insert date using date time in UK style date ?
Forget about regional datesettings. Follow this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hi Pinal,
I need to calculate the number of days by doing this calculation
MTRY_DT – (YR_NUM+MO_NUM+Last day of the month) ,Where MTRY_DT = Maturity Date . So,how can I do this for all the rows that I have.
I have 6446 rows.
Thanks in Advance,
Samyuktha
sir, i want to auto generated in date and month of the database in sql server 2005 . please help for me
You need to post some sample data and expected result to help you
please help me,I want to retrieve one column of my Database as per current date in asp.net ,SqlServer2005
Hello,
How can I specify a condtion to achieve the required date where
“required date= current date -13months”
RequiredDate = (sysdate – interval ’13′ month)
Thank you for your time Garethpn. But I get an error. sysdate is not a recognized function. and if it is GETDATE() -interval’13′month, I have an error “incorrect syntax near month”
Sysdate only works in ORACLE. In SQL Server you need to use getdate()
RequiredDate = dateadd(month,-13,getdate())
thanks pinal for ur help. can u pls help in some question….is SQL/PLI is similar to ODBC????also to JDBC?????
hi
i want to fetch the data according to particular time i select. suppose i want to fetch data between date 10 to 15 and for particular hr 16:00 then what is the suitable query for that. kindly help me
hi I am Ram,
I want to get last one year records …how can i get it..
i used this following SP. to get it.
alter procedure dev_GetAllWeekCharts_M
@userID bigint,
@StartDate datetime
as
BEGIN
declare @EndDate datetime = dateadd(Year, -1, @StartDate)
SELECT [weight],convert(varchar,Updateddate,101)[Updateddate] into #tempWeight from dev_UserAlerts_WeightTracking
where userid = @Userid and UpdatedDate between @EndDate and @StartDate
;with aweek(day,weight) as
( select @StartDate as day,’0′ as weight
union all
select day – 1,’0′ as weight from aweek
where day > @EndDate )
select aweek.day as WeightDate ,case when #tempWeight.weight IS NULL then ’0′ else #tempWeight.weight end as weight
from aweek left outer join #tempWeight on #tempWeight.Updateddate = aweek.day order by WeightDate asc
Drop table #tempWeight
END
exec dev_GetAllWeekCharts_M 4,’2012/04/02′
But is show error message as
(5 row(s) affected)
Msg 530, Level 16, State 1, Procedure dev_EvolveSystems_GetAllWeekCharts_M, Line 14
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
thanks.
RAM
Please tell me how get MIN Amount Date between 2011-12-31 and 2012-03-31 below table
Date Amount
2011-11-01 4,500.00
2012-02-15 5,300.00
2012-03-16 6,000.00
hi can u help me plz i want to get all data from current date to last date till which schedule hv been created from schedule tabel that contain date column
dear frindes please, who can help me i want to get only year from the database and then subtract a value from it. plz looking for your kind help.
@Mirwais,
In order to just extract the year, use command
select DATEPART(year,getdate())
Example of how to substract value from it:
select 2014 – DATEPART(year,getdate())
i wan to retrive data from existing table but i can’t compare datefield’s date(18-APR-2012) to current month
vishal pandey
dear friend i am also best make sql softwere
hello,
I have tried
DECLARE @X int
SET @X= DAY(getdate())
and the same things for year and month
and I increase DAY and I want to make that values in datetime (yyyy-mm-dd)
how can I do that
Simply use like
select dateadd(day,2,getdate())
actually my problem is that I want to get date from my existing table and increase day then format new datetime
Hi sir i want to fetch date from system but the condition is that
I want to fetch yesterdays and current date before 5:30 pm and the date must be not in 2nd and Third saturday and other sundays
HI..
I working on a oracle db but my db creashed suddenly,how can i take backup of my db.plz help
Only way is to restore the dump you have already taken
Thanks …giving first page in Google search
Sachin Karche
How insert cuurect date time in MSSQL table ??
Anyone Plz help…
use GETDATE()
insert into table(col)
select getdate()
This syntax is used to display only date (2011-11-01 00:00:00 )
eg syntax:
convert(varchar(10),Date_Da,101) as Date_Da
This Date_Da that field is our table date field
sir;
i am facing a problem .i want to find current date and time of indian format .
when we host the application then time is not in good format as like pm to change in am
sir, plz help me
I’m wanting to find records that are older than 85 days from today. When I put the following code: Category_3 < dateadd(day, -85, getdate())
Once excuted I then get the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Error Code: 242
Any ideas on how to fix this?
I fixed my own issue. Should have had:
Category_3 < Convert(Varchar(10),dateadd(day, -85, getdate()))
However now would love to sort these results in DESC order. ORDER BY Category_3 DESC doesn't work (I assume because of the Varchar). Any ideas?
I want month and year-wise Reports so how to pass Month and Year Parameters in Same Stored Procedure..Please Help Me as soon as
[...] Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} [...]
This is a very helpful article…Thanks PinalDave
how to find currenttime in australia
Thanks yet again… PinalDave, I have used your teachings many times and well, never offered a thank you. So… Thank you. Anytime I do a query for SQL help, I check your links first! You have been most helpful so many times – so thanks for this one, and all of them!
hi, how to get yyyymmddhhmmss ??? without space and : between hour minutes and seconds
Thanx
This is formation issues that should be handled by your application. However here is the answer for sql
select convert(char(8),getdate(),112)+replace(convert(char(10),getdate(),108),’:',”)
Hi Sir,
I m facing a problem that is:
I have two columns as CreateDate and ExpiryDate in ASPNETDB.MDF file, I want to put days between two date in DaysLeft Column.
I m using Visual Web developer 2005 express Edition. I m using the function
Datediff(Day,convert(datetime,CreateDate),Convert(Datetime,ExpiryDate)) in computed column specification
but failed. Please reply
I am creating an SSIS to export queried data to excel. I want to pull records from yesterdays date. Seems like the following should work but I get no return
SELECT * FROM TableName
WHERE dateTimeMix = dateadd(dd, -1, getdate())
It should be
SELECT * FROM TableName
WHERE dateTimeMix >= dateadd(day, datediff(day,-1, getdate())),0) and
dateTimeMix < dateadd(day, datediff(day,0, getdate())),0)
Hi Sir, can you please assist me I am trying to insert the current time in a timestamp field.
like
create table x ( a timestamp not null)
INSERT INTO x
VALUES CAST(current_timestamp AS TIMESTAMP)
This alone works
SELECT CAST(current_timestamp AS TIMESTAMP) but if I want to insert it into a table it does not work . I am aware that it converts it to binary on the table when it was successful