Date Convert from yyyy-mm-dd to dd/mm/yyyy

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

General SQL Server Forums

New to SQL Server Programming

Date Convert from yyyy-mm-dd to dd/mm/yyyy

Author

Topic

bashu
Starting Member

6 Posts

Posted - 2007-06-14 : 03:22:27

Hi to ALL
Here I am using .net 2.0 and MS SQL 2K. In our database table DateTime saved as in the format of 2007-01-31 8:33:19.000(yyyy-mm-dd) to access to this records by searching based on Date, when we are searching based on Date that Date format would be (dd/mm/yyyy). How we can convert the date format.

Data Base Date Format: 2007-01-31 (yyyy-mm-dd)
Search Criteria Date Format: 31/01/2007 (dd/mm/yyyy)

I have written following Code: Pls suggest me whether its correct r not

----------------------------------------------------------------------------
Select distinct tbl_adminuser.adminUserName,tbl_adminCategory.Name, COUNT(dbo.tbl_outbox.msgUserID) As

TotalCount

FROM dbo.tbl_adminuser,dbo.tbl_AdminCategory, dbo.tbl_outbox

where tbl_adminuser.adminUserID = dbo.tbl_AdminCategory.CatID and tbl_AdminCategory.CatID =

dbo.tbl_outbox.msgUserID

and tbl_outbox.msgUserID <> 0 and Convert(varchar,tbl_outbox.msgDate,103)>=@fromdate and
convert(varchar,tbl_outbox.msgDate,103)<=@todate
group by tbl_adminuser.adminUserName, dbo.tbl_AdminCategory.Name
--------------------------------------------------------------------------------------

Thanks in Advance
Bashu

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 03:42:59

Dates are stored in an internal format, so you don't need to worry about the format, just make sure you are comparing DateTime datetypes, and NOT strings!

The only guaranteed way to use string dates is 'yyyymmdd' - note that there is no punctuation.

So make sure that your @fromdate and @todate are using DateTime datatype, and then you don;t need the CONVERT at all.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 03:46:03

If you cannot easily provide date parameters in 'yyyymmdd' format then pass then as strings and make an explicit conversion to a DateTime datatype variable, and then use that within your query:


DECLARE @strDate	varchar(10),
	@dtDate		datetime

SET	@strDate = '31/01/2007'	-- (dd/mm/yyyy)

SELECT	@dtDate = CONVERT(datetime, @strDate, 103)

SELECT	@dtDate

Kristen

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 03:52:31

As wriiten by Kristen, It better to convert the variable instead of converting the column..
reason is no need to convert all the column values.

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 08:37:06

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources