convert date - 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)

convert date

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 02:15:28

Hi everyone, i would like to change the date into dd/mm/yyyy please help..

=DateValue(DateAdd("M",-1,DateAdd("D",-(Day(Now)-1),Now)))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:29:16

why should you do it in t-sql. this is a formatting issue which is best dealt at your front end. you shouldnt be worried on how dates are stored in table. while passing date values also pass it in universal format yyyymmdd
if you've no front end. make use CONVERT function

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:32:37

it would be something like


select CONVERT(varchar(10),datefield,105) from table

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 02:33:02

i use this in the report parameter default value. so that in my preview page of my ssrs i can have the date out. but currently 9/1/2011

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 02:47:59

select CONVERT(varchar(10),datefield,105) from table

this is done to edit the database record date. however the =DateValue(DateAdd("M",-1,DateAdd("D",-(Day(Now)-1),Now))) i need is to change the date to dd/mm/yyyy in my filter for my preview tab. it does not look at the database record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:54:19

quote:
Originally posted by somenoob

i use this in the report parameter default value. so that in my preview page of my ssrs i can have the date out. but currently 9/1/2011

you can use below convert for label. for value use actual date value itself

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 03:03:58

err i am sorry but i do not understand what you mean..

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 03:17:56

i have done it this way

=datevalue(Format(DateAdd("M",-1,DateAdd("D",-1(Day(Now)-1),now)),"dd/MM/yyyy"))

and it works to get the start of previous month 1/9/2011
but when i do the same for this

=DateValue(Format(DateAdd("D",-1,DateAdd("D",-(Day(Now)-1),Now)),"dd/MM/yyyy"))

it does not work. please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 03:57:09

quote:
Originally posted by somenoob

err i am sorry but i do not understand what you mean..

Every parameter will have a prompt and value. i was telling you need to worry about format only for the prompt not for value.

for getting start of previous month use
=Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900"))

and for getting previous day start use

=Dateadd(DateInterval.Day,datediff(dateinterval.Day,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900"))

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:00:30

for formatting use format() function as

=Format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"MM/dd/yyyy")

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 04:10:06

i tried this,
=Format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"MM/dd/yyyy")

But in my report parameters default value, there is a red underline at the month at line Dateadd(DateInterval.Month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:19:07

quote:
Originally posted by somenoob

i tried this,
=Format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"MM/dd/yyyy")

But in my report parameters default value, there is a red underline at the month at line Dateadd(DateInterval.Month

did you try running it? try previewing report first

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 04:24:27

i tried =Format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"dd/MM/yyyy")

it gave me the error,the value expression for the report parameter 'end' contains an error: [BC30198] ')' expected.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:51:08

its working for me

i've tried both inside report parameters as well as inside textbox.
Are you sure you're using this inside sql reporting services expression editor?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:51:48

even this will work


=Format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,#01/01/1900#,Now())-1,#01/01/1900#),"MM/dd/yyyy")

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 04:57:20

i am doing it in here

after i type in :

=format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"dd/mm/yyyy")

=format(Dateadd(DateInterval.Day,datediff(dateinterval.Day,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"dd/mm/yyyy")

when i preview in the preview tab,it shows
the preperty 'defaultvalue' of report parameter 'start' doesn't have the expected type

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:26:47

you're using non queried type for default value right?

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 05:29:31

yes. but i do not know how to write the code to get the last day of previous month in end and first day of previous month for start by dd/mm/yyyy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:38:26

quote:
Originally posted by somenoob

yes. but i do not know how to write the code to get the last day of previous month in end and first day of previous month for start by dd/mm/yyyy

thats what expression does.

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

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-11 : 05:40:30

how do i write the expression?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:46:44

=format(Dateadd(DateInterval.Month,datediff(dateinterval.Month,Cdate("01/01/1900"),Now())-1,Cdate("01/01/1900")),"dd/mm/yyyy")

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources