getting problem in fetching values

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

Transact-SQL (2008)

getting problem in fetching values

Author

Topic

partap26
Starting Member

27 Posts

Posted - 2013-06-03 : 03:44:24

declare @FormatNumber as nvarchar(12)
set @FormatNumber = 'select date_format from control_pannel where login_id="salathia"'
Select convert(at_date, @FormatNumber)//Error Line as Type at_date is not a defined system type.//
FROM alarm_time;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 03:54:35

do you mean at_time field contain the datatype info ie datetime etc? if yes, you need to use dynamic sql for convert part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-03 : 04:01:22

at_date is column name of alarm_time which contains the dates as 2013-06-01, 2013-05-31 and date_format for salathia user contains MM-dd-yyyy date format... i want output on the base of this query should be as 06-01-2013, 06-31-2013

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:17:21

i would have done the formating at front end and brought the date value as is from table.
Howeever if thats not possible only way is to use CONVERT function

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:25:27

so it should be


Select convert(varchar(20),at_date, @FormatNumber)
FROM alarm_time

in yourcase

One question here is do you've any field in alarm_time which gives associated user information?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-03 : 04:35:19

Its not working, displaying conversion error
and i have no user field in alarm_time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:44:10

quote:
Originally posted by partap26

Its not working, displaying conversion error
and i have no user field in alarm_time

that will work only if all your date values are of consistent format as per the number passed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 04:48:22

quote:
Originally posted by partap26

at_date is column name of alarm_time which contains the dates as 2013-06-01, 2013-05-31 and date_format for salathia user contains MM-dd-yyyy date format... i want output on the base of this query should be as 06-01-2013, 06-31-2013

See this example
DECLARE @At_Date DATE = '2013-06-01', @FormatNumber INT = 110
SELECT CONVERT(VARCHAR(20), @At_Date, @FormatNumber )

Refer the below link for different types of date formats
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:54:42

quote:
Originally posted by bandi

quote:
Originally posted by partap26

at_date is column name of alarm_time which contains the dates as 2013-06-01, 2013-05-31 and date_format for salathia user contains MM-dd-yyyy date format... i want output on the base of this query should be as 06-01-2013, 06-31-2013

See this example
DECLARE @At_Date DATE = '2013-06-01', @FormatNumber INT = 110
SELECT CONVERT(VARCHAR(20), @At_Date, @FormatNumber )

Refer the below link for different types of date formats
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

--
Chandu

I think issue OP is having is base datatype not being datetime and values having all different date formats which is cauisng it to break.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

partap26
Starting Member

27 Posts

Posted - 2013-06-03 : 05:21:48

but my requirment is as
declare @FormatNumber as nvarchar(12)
set @FormatNumber = 'select date_format from control_pannel where login_id="salathia"'
Select convert(varchar(20),at_date, @FormatNumber)
FROM alarm_time;

@FormatNumber contains string value as MM-dd-yyyy for date fomrat

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-03 : 05:28:53

quote:
Originally posted by partap26

but my requirment is as
declare @FormatNumber as nvarchar(12)
set @FormatNumber = 'select date_format from control_pannel where login_id="salathia"'
Select convert(varchar(20),at_date, @FormatNumber)
FROM alarm_time;

@FormatNumber contains string value as MM-dd-yyyy for date fomrat

you can use FORMAT function (which is available in Denali i.e. MSSQL 2012)
SELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY]

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 05:46:55

quote:
Originally posted by partap26

but my requirment is as
declare @FormatNumber as nvarchar(12)
set @FormatNumber = 'select date_format from control_pannel where login_id="salathia"'
Select convert(varchar(20),at_date, @FormatNumber)
FROM alarm_time;

@FormatNumber contains string value as MM-dd-yyyy for date fomrat

If you're on 2012 you can use Bandis suggestion

Otherwise you've to do it by using CASE WHEN based on your FormatNumber values.
like
CASE @FormatNumber
WHEN 'MM-dd-yyyy'
THEN CONVERT(varchar(11),at_date,110)
WHEN 'dd MMM yyyy'
THEN CONVERT(varchar(11),at_date,113)
...
END

I had thought it will have style numbers 110,121, etc rather than strings themselves seeing the variable name!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources