使用动态sql时返回日期值？ [英] Return date value while using dynamic sql?

问题描述

问候语。我正在写一些会为我生成输入语句的东西。因此，与服务器1相比，它将根据日期字段插入服务器以丢失数据。输入将是要过滤的架构，表和列。我用
挣扎的最后一块是将日期列作为实际日期返回，同时通过动态sql生成。

Greetings. I'm writing something that will generate input statements for me. So it will insert into Server to where data is missing compared to Server 1, based on a date field. The input will be schema, table, and columns to filter on. The last piece I'm struggling with is getting the date column to return as an actual date while being generated via dynamic sql.

DECLARE @maxDateColumn NVARCHAR(1024)
DECLARE @maxDateColumnRemote NVARCHAR(1024)
DECLARE @maxDate DATETIME
DECLARE @serverName NVARCHAR(128)
DECLARE @remoteServer NVARCHAR(128)
DECLARE @remoteServerColumns NVARCHAR(max)
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @dbName NVARCHAR(128)
DECLARE @exec nVarchar(max)
DECLARE @columns NVARCHAR(max) = '';


--these guys need to have values supplied
SET @tableSchema = 'mySchema'
SET @tableName = 'myTable'
SET @maxDateColumn = 'myColumn'
SET @maxDateColumnRemote = @maxDateColumn

SET @serverName = @@SERVERNAME
SET @dbName = DB_NAME()
SET @maxDateColumn = 'SELECT MAX ('+CONVERT(NVARCHAR(10), @maxDateColumn, 101) +') 
FROM '+@serverName+'.'+@dbName+'.'+@tableSchema+'.'+@tableName+''

EXEC (@maxDateColumn)
SELECT @maxDateColumn

--SET @maxDate = (CONVERT(DATETIME,@maxDateColumn))

SET @maxDate = (CAST(@maxDateColumn AS datetime))
--SELECT @maxDate

EXEC（@maxDateColumn）返回查找日期的信息，但是你会看到它真的是一个角色。我需要那个作为实际日期时间返回的人在我动态生成的语句中进一步使用。

The EXEC (@maxDateColumn) returns date looking information, but it's really a character as you'll see. I need that guy returned as an actual datetime to use further on in my dynamically generated statements.

谢谢！

提前致谢！ ChrisRDBA

Thanks in advance! ChrisRDBA

推荐答案

如果对CONVERT（）函数使用代码112而不是101，您将获得一个字符串表示形式，稍后将对其进行解释正确地作为日期时间值（并且没有时间组件）。

If you use the code 112 instead of 101 for the CONVERT() function, you'll get a string representation that will later be interpreted as a datetime value correctly (and with no time component).

这篇关于使用动态sql时返回日期值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！