DATE FORMAT CONVERSION - 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 2005 Forums

Transact-SQL (2005)

DATE FORMAT CONVERSION

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 04:43:01

Hi everyone,
I am using 2 date formats.
1. dd/mm/yyyy
2. dd-mm-yyyy
in the same code.But I am not getting the result for the 2nd one. Instead date is appearing in the 1st format only.

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 04:48:44

quote:
Originally posted by smitha

Hi everyone,
I am using 2 date formats.
1. dd/mm/yyyy
2. dd-mm-yyyy
in the same code.But I am not getting the result for the 2nd one. Instead date is appearing in the 1st format only.

Smitha

You should always express your dates in YYYYMMDD HH:MM:SS

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 04:59:11

But I am assigning the date toa avariable like this
sNowDate1 = Format(Now(), "dd/mm/yyyy")
sNowDate2 = Format(Now(), "dd-mm-yyyy")
I want both the formats in the code. As I said it is taking only the 1st one but not the second one.

Smitha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 05:42:05

Looks like:
- MySQL and this is a MS SQL Server Forum
- someone who will give help needs to see the code.

Maybe you can get better help in a MySQL forum or am I Wrong?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 05:52:08

quote:
Originally posted by smitha

But I am assigning the date toa avariable like this
sNowDate1 = Format(Now(), "dd/mm/yyyy")
sNowDate2 = Format(Now(), "dd-mm-yyyy")
I want both the formats in the code. As I said it is taking only the 1st one but not the second one.

Smitha

why should you be concerned about formats while storing dates? you can always display them in way you want using format functions at front end

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 05:54:30

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 05:58:29

quote:
Originally posted by smitha

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

you dont need to worry on that. can i ask reason for asking this?

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 06:05:07

b'cos I am storing the values along with the date from end to
sql. At that time whatever the format I give from front end, the Sql is taking the date in mm/dd/yy format only

quote:
Originally posted by visakh16

quote:
Originally posted by smitha

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

you dont need to worry on that. can i ask reason for asking this?

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 06:15:27

quote:
Originally posted by smitha

b'cos I am storing the values along with the date from end to
sql. At that time whatever the format I give from front end, the Sql is taking the date in mm/dd/yy format only

quote:
Originally posted by visakh16

quote:
Originally posted by smitha

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

you dont need to worry on that. can i ask reason for asking this?

Smitha

provided your datatype used is datetime it will be stored in sql table in yyy-mm-dd hh:mi:ss format. you can then change this to format you want by using front end formatting functions. attempt to change it with table will require change in datatype as well which will complicate date manipulations in your queries so its not at all recommended.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 06:15:37

quote:
Originally posted by smitha

b'cos I am storing the values along with the date from end to
sql. At that time whatever the format I give from front end, the Sql is taking the date in mm/dd/yy format only

quote:
Originally posted by visakh16

quote:
Originally posted by smitha

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

you dont need to worry on that. can i ask reason for asking this?

Smitha

Have you tried what I said?
Dont worry how dates are stored in table
It is your front end that should do formation

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 06:17:37

Ya, I tried with your given format also. but it is showing error as type mismatch

quote:
Originally posted by madhivanan

quote:
Originally posted by smitha

b'cos I am storing the values along with the date from end to
sql. At that time whatever the format I give from front end, the Sql is taking the date in mm/dd/yy format only

quote:
Originally posted by visakh16

quote:
Originally posted by smitha

that's what I am doing in the front end. Is there any other way I can change the date format in SQL table only

Smitha

you dont need to worry on that. can i ask reason for asking this?

Smitha

Have you tried what I said?
Dont worry how dates are stored in table
It is your front end that should do formation

Madhivanan

Failing to plan is Planning to fail

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 06:20:47

is your field datatype datetime?

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-13 : 06:26:41

yes it is

quote:
Originally posted by visakh16

is your field datatype datetime?

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 08:15:11

quote:
Originally posted by smitha

yes it is
quote:
Originally posted by visakh16

is your field datatype datetime?

Smitha

Post the code you used

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:16:54

quote:
Originally posted by smitha

yes it is
quote:
Originally posted by visakh16

is your field datatype datetime?

Smitha

can you try running trace and see how the date value is getting passed from front end app?

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 08:32:49

What database brand are you using?

and what language is:


sNowDate1 = Format(Now(), "dd/mm/yyyy")
sNowDate2 = Format(Now(), "dd-mm-yyyy")

because I don't recognise that as being Microsoft SQL.

This may be effecting the advice you are getting here (as this site is specifically for Microsoft SQL Server - folk may know other SQL dialects, but they will be assuming you are talking about MS SQL unless you say otherwise

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-13 : 08:47:22

quote:
Originally posted by Kristen

What database brand are you using?

and what language is:

sNowDate1 = Format(Now(), "dd/mm/yyyy")
sNowDate2 = Format(Now(), "dd-mm-yyyy")

because I don't recognise that as being Microsoft SQL.

This may be effecting the advice you are getting here (as this site is specifically for Microsoft SQL Server - folk may know other SQL dialects, but they will be assuming you are talking about MS SQL unless you say otherwise )

I think it is VB6

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-13 : 09:15:25

I think the answer is easy.

Since you have a table in a SQL Server database and the data type of your column is datetime you should not care about how sql is managing the date in the database and do not care about the format of the date in the table.

If you want to display a date in your front end using visual basic or what ever then use the format commands of that language.

If the formatting will not work then you can better ask in a forum that handles your programming language.

And by the way: sNowDate1 = Format(Now(), "dd/mm/yyyy") has nothing to do with SQL.

Greetings
Webfred

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources