Help regarding converting 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)

Help regarding converting date

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 01:43:13

Hi all,
in my company from application all dates are come into dd/mm/yyyy format in a string . and i want to convert it into mm/dd/yyyy format.
is there any function in sql server 2005 which will convert it.
like in oracle to_date function works.
i am newbie to SQL server 2005.

any help appreciated.

Pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:45:45

yup. you can use CONVERT function for that.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 01:49:46

So you want to store that incoming string 'dd/mm/yyyy' as a datetime in your database?
Then there is no need to convert it into another string, just convert it to datetime data type.
convert(datetime,'your_string')
Later if you want to display your date in a wanted format you can convert that formerly stored value to display like you want.
See CONVERT in BOL.

But always store datetime values as datetime data type in your database please.

No, you're never too old to Yak'n'Roll if you're too young to die.

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 01:57:19

Hi Thanks for quick reply,

please check this

'01/10/2009' ---- dd/mm/yyyy format
my incoming date is in this format.
if i convert it into datetime
SELECT CONVERT(DATETIME , '01/10/2009')

it gives me 10 jan 2009

SELECT CONVERT(VARCHAR,CONVERT(DATETIME , '01/10/2009') ,103)

quote:
Originally posted by webfred

So you want to store that incoming string 'dd/mm/yyyy' as a datetime in your database?
Then there is no need to convert it into another string, just convert it to datetime data type.
convert(datetime,'your_string')
Later if you want to display your date in a wanted format you can convert that formerly stored value to display like you want.
See CONVERT in BOL.

But always store datetime values as datetime data type in your database please.

No, you're never too old to Yak'n'Roll if you're too young to die.

Pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:58:36

quote:
Originally posted by webfred

So you want to store that incoming string 'dd/mm/yyyy' as a datetime in your database?
Then there is no need to convert it into another string, just convert it to datetime data type.
convert(datetime,'your_string')
Later if you want to display your date in a wanted format you can convert that formerly stored value to display like you want.
See CONVERT in BOL.

But always store datetime values as datetime data type in your database please.

No, you're never too old to Yak'n'Roll if you're too young to die.

I have had instances where passing date in dd/mm/yyyy format throws error if server setting is US. the error message will be syntax error converting datetime from character string. thats why i always try to use iso format (yyyy-mm-dd)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:02:14

<<
thats why i always try to use iso format (yyyy-mm-dd)
>>

Better you use YYYYMMDD format. That is truely unambiguous

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:04:59

quote:
Originally posted by pmotewar

Hi Thanks for quick reply,

please check this

'01/10/2009' ---- dd/mm/yyyy format
my incoming date is in this format.
if i convert it into datetime
SELECT CONVERT(DATETIME , '01/10/2009')

it gives me 10 jan 2009

SELECT CONVERT(VARCHAR,CONVERT(DATETIME , '01/10/2009') ,103)

quote:
Originally posted by webfred

So you want to store that incoming string 'dd/mm/yyyy' as a datetime in your database?
Then there is no need to convert it into another string, just convert it to datetime data type.
convert(datetime,'your_string')
Later if you want to display your date in a wanted format you can convert that formerly stored value to display like you want.
See CONVERT in BOL.

But always store datetime values as datetime data type in your database please.

No, you're never too old to Yak'n'Roll if you're too young to die.

Pankaj

as i said its because your server setting is US. try passing a date like 15/10/2009 and it should error.
i think you need to pass it in iso format as me and Madhi told

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 02:05:19

set language german -- is not changing the whole server setting, it's just for this session
SELECT CONVERT(DATETIME , '01/10/2009')

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 02:07:14

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 02:15:53

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

Pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:18:37

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

Pankaj

if date is coming from app, apply dateformating functions there to convert it to iso format

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 02:22:00

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

Pankaj

looks good in my test:


set language english

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

set language german

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

always the same result.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:26:07

In that case you can also use

set dateformat dmy
SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

Madhivanan

Failing to plan is Planning to fail

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 02:31:26

quote:
Originally posted by visakh16

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct
SELECT CONVERT(DATETIME , '01/10/2009' , 103)
SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)
Pankaj

if date is coming from app, apply dateformating functions there to convert it to iso format

hi,
i will first clear the functionality i want,
i want to insert a record into the table. out of all column some are having datetime data type. we store only date part ( not time).
now from my application all inputs for procedure is string type.
all incoming date parameters are string type and date coming as 'dd/mm/yyyy'. when i try to insert this it gives me error . i want to handle it in procedure itself.
now,
is there any need to convert date in ISO format in application and send it to procedure ?
and is my above convert function used as a solution ?

Pankaj

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 02:33:57

quote:
Originally posted by webfred

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

Pankaj

looks good in my test:

set language english

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

set language german

SELECT CONVERT(DATETIME , '01/10/2009' , 103)

SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)

always the same result.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks webfred

Pankaj

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:36:58

Read my previous suggestion

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:37:28

quote:
Originally posted by pmotewar

quote:
Originally posted by visakh16

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct
SELECT CONVERT(DATETIME , '01/10/2009' , 103)
SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)
Pankaj

if date is coming from app, apply dateformating functions there to convert it to iso format

hi,
i will first clear the functionality i want,
i want to insert a record into the table. out of all column some are having datetime data type. we store only date part ( not time).
now from my application all inputs for procedure is string type.
all incoming date parameters are string type and date coming as 'dd/mm/yyyy'. when i try to insert this it gives me error . i want to handle it in procedure itself.
now,
is there any need to convert date in ISO format in application and send it to procedure ?
and is my above convert function used as a solution ?

Pankaj

i think as your date values come as string from app it will be best if you can convert it to iso format there.
if not,you could convert it to date value in procedure using convert

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-10-01 : 04:37:57

quote:
Originally posted by visakh16

quote:
Originally posted by pmotewar

quote:
Originally posted by visakh16

quote:
Originally posted by pmotewar

quote:
Originally posted by webfred

I think the OP said: Date is coming from app in this format, hence he cannot change it and in this case and has to work around it.

No, you're never too old to Yak'n'Roll if you're too young to die.

is this correct
SELECT CONVERT(DATETIME , '01/10/2009' , 103)
SELECT CONVERT(VARCHAR, CONVERT(DATETIME , '01/10/2009' , 103),103)
Pankaj

if date is coming from app, apply dateformating functions there to convert it to iso format

hi,
i will first clear the functionality i want,
i want to insert a record into the table. out of all column some are having datetime data type. we store only date part ( not time).
now from my application all inputs for procedure is string type.
all incoming date parameters are string type and date coming as 'dd/mm/yyyy'. when i try to insert this it gives me error . i want to handle it in procedure itself.
now,
is there any need to convert date in ISO format in application and send it to procedure ?
and is my above convert function used as a solution ?

Pankaj

i think as your date values come as string from app it will be best if you can convert it to iso format there.
if not,you could convert it to date value in procedure using convert

Thanks

Pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 04:45:11

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources