quote:

---

Originally posted by visakh16

is the target field varchar? if not, it will be displayed as 2008-01-01 00:00:00.000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

no need of convert then as 12:00 AM corresponds to 00:00:00 in sql server when storing dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Kristen

Best to format your date in your application.

But if there is no other way you can do it in SQL Server by converting it to a string and using a format-parameter

CONVERT(varchar(99), MyDate, 999)

were 99 is the width you want, and 999 is the formatting style (see documentation for a list of available types)

You should NOT then pass the date to your application in that format, as it will be a String and not a Date / Datetime object.

But from what you have said your date already has 00:00:00.000 time element - when you view it you are using seeing that as "midnight" in whatever the default display format is. Anything you use, in your application, to display just the date part will not have any time because "midnight" is "zero time"

---

quote:

---

Originally posted by gwilson67

Have you tried convert(date, getdate())


Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development

---

DECLARE @datetime1 datetime, @datetime2 datetime, @date3 date

SELECT	@datetime1 = '01/01/2008 12:00:00AM',
	@datetime2 = '20080101 00:00:00',
	@date3 = '20080101'

SELECT	@datetime1, @datetime2, @date3

SELECT	[1 and 2] = CASE WHEN @datetime1 = @datetime2 THEN 'Same' ELSE 'Different' END
SELECT	[1 and 3] = CASE WHEN @datetime1 = @date3 THEN 'Same' ELSE 'Different' END

quote:

---

Originally posted by Kristen

"In my sql testing (date portions equal) a date from the database with a 00:00:00 time will not compare correctly with a my screen date of 12:00:00 AM"

Something wrong with your "screen date" then:

DECLARE @datetime1 datetime, @datetime2 datetime, @date3 date

SELECT	@datetime1 = '01/01/2008 12:00:00AM',
	@datetime2 = '20080101 00:00:00',
	@date3 = '20080101'

SELECT	@datetime1, @datetime2, @date3

SELECT	[1 and 2] = CASE WHEN @datetime1 = @datetime2 THEN 'Same' ELSE 'Different' END
SELECT	[1 and 3] = CASE WHEN @datetime1 = @date3 THEN 'Same' ELSE 'Different' END

Results are that 1&2 are the same, and 1&3 are the same ...

---