convert sqlServer varchar date to c# date

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 2005 Forums

Transact-SQL (2005)

convert sqlServer varchar date to c# date

Author

Topic

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-27 : 07:54:47

I am using SQL server 2005 database.

I have a varchar field which stores date in the format "DD-MM-YYYY"

now what i want to do is, convert this date from database(varchar format) to c# date time format but if i use DateTime.parse it considers the DD as MM and MM as DD.

This is basically a expiry date and i want to compare this with today date and see if the expiry is happening within next 15 days.

So if can do this is in 1 query it will be great.

I cannot change the field varchar to Date, as the project is almost complete and i don't want to add additional work :-)

Thanks in advance

www.JamboreeBliss.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 08:00:27

1 Always use proper DATETIME datatype to store dates
2 set dateformat dmy
Your select query that convert the date to datetime
ex
set dateformat dmy
select cast('19-12-2009' as datetime)

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-27 : 08:03:55

Hi bhanu2217
May be you expect this..


CREATE TABLE #TEMP(ID INT IDENTITY(1,1), DATE VARCHAR(12))

INSERT INTO #TEMP
SELECT '11-05-1983'

SELECT CONVERT(DATETIME,DATE) FROM #TEMP

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 08:07:12

quote:
Originally posted by rajdaksha

Hi bhanu2217
May be you expect this..

CREATE TABLE #TEMP(ID INT IDENTITY(1,1), DATE VARCHAR(12))

INSERT INTO #TEMP
SELECT '11-05-1983'

SELECT CONVERT(DATETIME,DATE) FROM #TEMP

-------------------------
R...

That wont help. See my first reply

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-27 : 08:11:41

hi

what is the difference


CREATE TABLE #TEMP(ID INT IDENTITY(1,1), DATE VARCHAR(12))

INSERT INTO #TEMP

SELECT '11-05-1983'

SET DATEFORMAT DMY

SELECT CAST(DATE AS DATETIME) FROM #TEMP

SELECT CONVERT(DATETIME,DATE) FROM #TEMP

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 08:19:13

See the result for '19-05-2009' with your first suggestion

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-27 : 08:23:25

Hi

yes need to change the format
SET DATEFORMAT DMY

thanks a lot madhi..

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 08:25:05

quote:
Originally posted by rajdaksha

Hi

yes need to change the format
SET DATEFORMAT DMY

thanks a lot madhi..

-------------------------
R...

Yes that was what my point

Madhivanan

Failing to plan is Planning to fail

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-27 : 08:28:39

quote:
Originally posted by madhivanan

1 Always use proper DATETIME datatype to store dates
2 set dateformat dmy
Your select query that convert the date to datetime
ex
set dateformat dmy
select cast('19-12-2009' as datetime)

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan

How do i execute my query is this.
My query is
select userid, delivery_end_date from db_registration
delivery_end_date is what i need to convert to dateTime.

or can i write a store procedure to return me the userid where delivery_end_date is within 15 days of expiry from today's date?

www.JamboreeBliss.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 08:35:44

set dateformat dmy
select userid, cast(delivery_end_date as datetime) from db_registration
where cast(delivery_end_date as datetime) <=dateadd(day,15,getdate())

Make it as a stored procedure

Madhivanan

Failing to plan is Planning to fail

bhanu2217
Starting Member

35 Posts

Posted - 2009-11-27 : 08:42:49

quote:
Originally posted by madhivanan

set dateformat dmy
select userid, cast(delivery_end_date as datetime) from db_registration
where cast(delivery_end_date as datetime) <=dateadd(day,15,getdate())

Make it as a stored procedure

Madhivanan

Failing to plan is Planning to fail

Thanks a lot :-)

www.JamboreeBliss.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources