sql query Using current Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sql query Using current Date

Author

Topic

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-01-31 : 02:12:27

Hi I am using Sql server 2005 Express I want to know how shall I retrieve all transaction from a table which is before the current Date .Please show me the sql query.I have used the following query
But it was not working properly

SELECT dbo.Inventory.ItemCode, dbo.ItemMaster.Description, dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, SUM(dbo.Inventory.qty)
AS Totalqty
FROM dbo.Inventory LEFT OUTER JOIN
dbo.ItemMaster ON dbo.Inventory.ItemCode = dbo.ItemMaster.ItemCode
WHERE (dbo.Inventory.Voucherdate > { fn NOW() })
GROUP BY dbo.Inventory.ItemCode, dbo.Inventory.ItemType, dbo.Inventory.BrandName, dbo.Inventory.Size, dbo.ItemMaster.Description

Keshab

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 02:13:26

Getdate().

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-02-01 : 00:11:01

Hi I itz okay but I want only the date part not time.

If I run the sql query like this...
Select getDate() as mydate

I get the following result....
2008-02-01 10:38:10.857 .I want to retrive only the date part

Keshab

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-01 : 00:19:10

Select Convert(varchar(20),getDate(),103) as mydate. Look Books online for various date formats like 103,101 etc. If you are using this in fron end, better will be to use front end application to format date returned from getdate().

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2008-02-01 : 09:53:32

Hi You have to convert the date part. try this
select Convert(Varchar(12), GetDate(), 112)

for date format you can change the numbers
Style ID Style Type
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

SQL IN Minds

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-01 : 10:47:12

quote:
Originally posted by Keshaba

Hi I itz okay but I want only the date part not time.

If I run the sql query like this...
Select getDate() as mydate

I get the following result....
2008-02-01 10:38:10.857 .I want to retrive only the date part

Keshab


select Date = dateadd(dd,datediff(dd,0,'2008-02-01 10:38:10.857'),0)

Date                                                   
------------------------------------------------------ 
2008-02-01 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources